Found this somewhere on web several months ago. Very useful for long lists of machines that one want to order by IP addresses.
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
|
Related here: Excel to text – https://eikonal.wordpress.com/2011/02/14/excel-to-text/ | Excel files processing – https://eikonal.wordpress.com/2011/02/25/excel-files-processing/ | IT tips pages – https://eikonal.wordpress.com/2010/02/08/it-tips-pages/
Eikonal Blog 