# Excel formula to complete missing gaps of IP addresses range

Hi,

I have an excel file which has 1 column, data starts in A2 cell (A1=heading)
lets say there's a range of:
191.192.192.0 - 191.192.195.254 in ascending order

I would like somehow to fill in the missing gaps in the correct position, for example:

191.192.192.1
191.192.192.2
191.192.192.3
<automatically insert 191.192.192.4>
191.192.192.5
191.192.194.50
191.192.194.51
<automatically insert 191.192.194.52>
<automatically insert 191.192.194.53>
191.192.194.54

I would like to get a complete solution / VBA script if possible.

Thanks a bunch.

P.S.
Excel 2010
###### Who is Participating?

RetiredCommented:
That's a bit more complicated, but try this.

Note that is only goes up to .65 instead of .255 for testing purposes. Change the constant value for production runs.
``````Option Explicit

Sub FillIn()
Dim r As Integer
Dim ip4 As Integer
Dim strFirstThree As String
Dim sh As Worksheet
Dim rng As Range

Const ip4Start = 0
Const ip4End = 66   'test value. Needs to be 256 if live

r = 1
ip4 = ip4Start
strFirstThree = Left(Cells(r, 1).Value, 11)
Do Until Cells(r, 1).Value = ""
Do While Left(Cells(r, 1).Value, 11) = strFirstThree
Do Until Split(Cells(r, 1).Value, ".")(3) = ip4
Rows(r).Insert xlShiftDown
Cells(r, 1).Value = strFirstThree & "." & ip4
ip4 = ip4 + 1
r = r + 1
Loop
Do While Left(Cells(r, 1).Value, 11) = strFirstThree And Split(Cells(r, 1).Value, ".")(3) = ip4
ip4 = ip4 + 1
r = r + 1
If Cells(r, 1).Value = "" Then
Exit Do
End If
Loop
Loop
Do Until ip4 = ip4End
Rows(r).Insert xlShiftDown
Cells(r, 1).Value = strFirstThree & "." & ip4
ip4 = ip4 + 1
r = r + 1
Loop
strFirstThree = Left(Cells(r, 1).Value, 11)
ip4 = ip4Start
Loop

End Sub
``````
0

Commented:
You could put 191.192.192.1 on cell A2 and 191.192.192.2 on cell A3 and then drag below to autofill the rest of the cells creating a list of all of the IP's...
0

Commented:
see the attached file click on command button it will work
0

Commented:
Dim count As Long
Dim newNumber As String
Dim last2() As String
Dim Splitnumber As Long

For count = 1 To 100

If Cells(count, 1) = "" Then
newNumber = Cells(count - 1, 1)
last2() = Split(newNumber, ".")
Splitnumber = last2(3) + 1
Cells(count, 1) = last2(0) + "." + last2(1) + "." + last2(2) + "." + CStr(Splitnumber)
'Cells(count, 2) = last2(3)
End If
Next
0

Commented:
But you're not filling all the gaps, e.g. between 191.192.192.5 and 191.192.194.50?
0

RetiredCommented:
Try this VBA
``````Option Explicit

Sub FillIn()
Dim r As Integer
Dim ip4 As Integer
Dim strFirstThree As String

r = 1
ip4 = Split(Cells(r, 1).Value, ".")(3) + 1
strFirstThree = Left(Cells(r, 1).Value, 11)
Do Until Cells(r, 1).Value = ""
r = r + 1
If Left(Cells(r, 1).Value, 11) = strFirstThree Then
If Split(Cells(r, 1).Value, ".")(3) > ip4 Then
Rows(r).Insert xlShiftDown
Cells(r, 1).Value = strFirstThree & "." & ip4
End If
End If
If Cells(r, 1).Value = "" Then
Exit Sub
End If
ip4 = Split(Cells(r, 1).Value, ".")(3) + 1
strFirstThree = Left(Cells(r, 1).Value, 11)
Loop
End Sub
``````
0

Commented:
if you run my programme it will work,its filling the all the gaps. untill 100 what he has to do is loop extend until 255
Dim count As Long
Dim newNumber As String
Dim last2() As String
Dim Splitnumber As Long

For count = 1 To 255

If Cells(count, 1) = "" Then
newNumber = Cells(count - 1, 1)
last2() = Split(newNumber, ".")
Splitnumber = last2(3) + 1
Cells(count, 1) = last2(0) + "." + last2(1) + "." + last2(2) + "." + CStr(Splitnumber)
'Cells(count, 2) = last2(3)
End If
Next
0

RetiredCommented:
Oops. I have included the heading row.

Therefore line 8 should be
r = 2
0

Author Commented:
I forgot to mention that on column B there's a description of the specific ip owner, meaning the data on columns a + b should stay on the same rows and keep in sync even when ip addresses fill in the gaps
0

RetiredCommented:
Well my macro inserts rows, so all the columns for the the rows below would move down.

ukerandi's macro assumes that there are spaces for the missing IPs so there is no row insertion.

Either way Column B should still match column A.

Have you tried the macros?
0

Author Commented:
Worked great except I need the macro to complete the range completely, for example:

the range 191.192.194 ends at .242 , i need the macro to complete the rest up to 254 , currently it stopped at .242 because that's the last address in that scope.

191.192.194.242
191.192.195.0
191.192.195.1

Thanks.
0

RetiredCommented:
I've also started on the wrong row again, so line 15 should be

r = 2
0

Author Commented:
Worked great, thanks again.
0

Author Commented:
worked gr8.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.