I have a range of IP Addresses (xx.xx.xxx.1 through .255). When I sort by
this column, I get xx.xx.xxx.0 through .99 followed by xx.xx.xxx.1 through
.199 and then 2 - 255. Is there a way to force it so that it sorts in a
sequencial 1,2,3,4... order?
Here is a VBA Macro that will sort IP addresses.
To enter it, <alt-F11> opens the VB Editor. Ensure your project is highlighted
in the project explorer windown, then Insert/Module and paste the code below
into the window that opens.
To use this, either select a range of cells, or select a single cell in the
area of the IP addresses. Then <alt-F8> opens the macro dialog box. Select
SortIP and RUN.
==========================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long, k As Long
Dim IP
Dim rg()
Dim RangeToSort As Range
Dim IPaddress As String
Dim IPColumn As Long
IPaddress = "#*.#*.#*.#*"
Set RangeToSort = Selection
'If just one cell selected, then expand to current region
If RangeToSort.Count = 1 Then
Set RangeToSort = RangeToSort.CurrentRegion
End If
'Check if row 1 contains an IP address. If not, it is a header row
'first find column with IP addresses.
'Check row 2 since row 1 might be a header
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
If IPColumn > RangeToSort.Columns.Count Then
MsgBox ("No valid IP address found in Row 1 or Row 2")
Exit Sub
End If
IPColumn = IPColumn + 1
Loop
If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
Set RangeToSort = RangeToSort.Offset(1, 0). _
Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
End If
'one extra column for the IP sort order
ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
rg(i, k) = RangeToSort.Cells(i + 1, k).Text
Next k
IP = Split(rg(i, IPColumn), ".")
For j = 0 To 3
rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
Next j
Next i
rg = BubbleSort(rg, 0)
For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
RangeToSort.Cells(i + 1, k) = rg(i, k)
Next k
Next i
End Sub
Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim Temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean
k = UBound(TempArray, 2)
ReDim Temp(0, k)
Do
NoExchanges = True
For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) > TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
Temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = Temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges
BubbleSort = TempArray
End Function
=========================================
--ron
