ExcelBanter - sorting IP addresses

On Mon, 5 Dec 2005 14:40:02 -0800, PattiTechWriter
wrote:

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