Eikonal Blog

2012.02.07

Excel sortIP macro

Filed under: transformers — Tags: , , — sandokan65 @ 14:39

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/

    Advertisements

3 Comments »

  1. […] Excel sortIP macro […]

    Like

    Pingback by Excel to text « Eikonal Blog — 2012.02.07 @ 14:42

  2. […] Excel Tips: http://www.rondebruin.nl/tips.htm Related here: Excel sortIP macro – https://eikonal.wordpress.com/2012/02/07/excel-sortip-macro/ | Excel to text – https://eikonal.wordpress.com/2011/02/14/excel-to-text/ | Excel files […]

    Like

    Pingback by IT tips pages « Eikonal Blog — 2012.02.07 @ 14:45


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: