Filtering columns in Excel based on Public IP address.

[abhishekdwivedi02]

Hi Using VBAcode, I want to filter data code which contains public IP Address and copy the filtered data to other sheet. Column contains both string and number. Please see example:-

ABC	http://192.168.44.29:2967	DENIED
BCD	https://d31qbv1cthcecs. atrk.js	DENIED
CDE	http://172.25.5.107/	DENIED
EFG	http://95.138.166.181/top-links.gif	Accepted
FGH	http://content.mail5.spopessentials5.com/	Accepted

<tbody>
</tbody>

In above table (second column) the URL address after http:// can start with string or can start with number. If it is starting with numbers the I need to check whether the address is public is Private or public. In second column http://192.168.44.29, http://172.25.5.107/is Private IP so I need to ignore it but http://95.138.166.181 is public IP and we need to copy to other sheet.

 

[Peter_SSs]

Welcome to the MrExcel board!

Assumptions ..

- Data is on a sheet called 'Data'

- Results to go on a sheet called 'Results'

- Results sheet already exists with at least headers in row 1

Give this a try in a copy of your workbook.

Sub Filter_Public_IPs()
  Dim a, b, Bits, aPteIPstart, aPteIPend
  Dim i As Long, j As Long, k As Long, UBa1 As Long, UBa2 As Long
  Dim bFail As Boolean
  Dim s As String
  Dim RX As Object
  
  Const sPteIPstart As String = "10.0.0.0.169.254.0.0.172.16.0.0.192.168.0.0"
  Const sPteIPend As String = "10.255.255.255.169.254.255.255.172.31.255.255.192.168.255.255"
  
  aPteIPstart = Split(sPteIPstart, ".")
  aPteIPend = Split(sPteIPend, ".")
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = False
  RX.Pattern = "^(\d{1,3}\.){3}\d{1,3}"
  
  With Sheets("Data")
    a = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 3).Value
  End With
  UBa1 = UBound(a, 1)
  UBa2 = UBound(a, 2)
  ReDim b(1 To UBa1, 1 To UBa2)
  For i = 1 To UBa1
    bFail = False
    s = Mid(a(i, 2), 8)
    If RX.test(s) Then
      Bits = Split(RX.Execute(s)(0), ".")
      j = -4
      Do
        j = j + 4
        If CLng(Bits(0)) >= CLng(aPteIPstart(j)) And CLng(Bits(0)) <= CLng(aPteIPend(j)) _
            And CLng(Bits(1)) >= CLng(aPteIPstart(j + 1)) And CLng(Bits(1)) <= CLng(aPteIPend(j + 1)) _
            And CLng(Bits(2)) >= CLng(aPteIPstart(j + 2)) And CLng(Bits(2)) <= CLng(aPteIPend(j + 2)) _
            And CLng(Bits(3)) >= CLng(aPteIPstart(j + 3)) And CLng(Bits(3)) <= CLng(aPteIPend(j + 3)) Then
          bFail = True
        End If
      Loop Until bFail Or j = 12
    Else
      bFail = True
    End If
    If Not bFail Then
      k = k + 1
      For j = 1 To UBa2
        b(k, j) = a(i, j)
      Next j
    End If
  Next i
  With Sheets("Results").UsedRange
    .Offset(1).ClearContents
    .Cells(2, 1).Resize(k, UBa2).Value = b
  End With
End Sub

 

[abhishekdwivedi02]

Hi Peter,
I tried with the code but its not returning any result.
While debugging I found value of a is coming empty.
If possible please explain me why we are using "RX.Pattern = "^(\d{1,3}\.){3}\d{1,3}""

I tried with following data (kept them in Data sheet in Column A).After running macro I am getting empty result in Results sheet.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 427"]
<tbody>[TR]
[TD]http://172.17.96.17[/TD]
[/TR]
[TR]
[TD]http://208.17.96.17[/TD]
[/TR]
[TR]
[TD]https://d31qbv1cthcecs.cloudfront.net/atrk.<acronym title="JavaScript" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">js</acronym>
[TABLE="class: cms_table, width: 427"]
<tbody>[TR]
[TD]http://95.138.166.181[/TD]
[/TR]
[TR]
[TD]http://95.138.166.181/Content/images..._top-links.gif[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Hi Peter,
I tried with the code but its not returning any result.
While debugging I found value of a is coming empty.
If possible please explain me why we are using "RX.Pattern = "^(\d{1,3}\.){3}\d{1,3}""

I tried with following data (kept them in Data sheet in Column A).After running macro I am getting empty result in Results sheet.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 427"]
<tbody>[TR]
[TD]http://172.17.96.17[/TD]
[/TR]
[TR]
[TD]http://208.17.96.17[/TD]
[/TR]
[TR]
[TD]https://d31qbv1cthcecs.cloudfront.net/atrk.<acronym title="JavaScript" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">js</acronym>
[TABLE="class: cms_table, width: 427"]
<tbody>[TR]
[TD]http://95.138.166.181[/TD]
[/TR]
[TR]
[TD]http://95.138.166.181/Content/images..._top-links.gif[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

That would be because your original sample appeared to have the IP data in column B (ABC, BCD etc in column A), so that is where my code is looking for it.
Try again putting this new sample data in B2:B6


RX.Pattern = "^(\d{1,3}\.){3}\d{1,3}"

I am using a "Regular Expression" pattern to look for what might be an IP address. The pattern is saying look for (1 to 3 digits followed by a dot) repeated 3 times followed by 1 to 3 digits

 

[abhishekdwivedi02]

Apologies,It was my mistake..Thanks alot sir.Its working fine.

 

[Peter_SSs]

Thanks alot sir.Its working fine.

Good to hear.