List to Range in Excel

I know that range means something else in Excel, what we are talking about here is making a list of numbers into a list of ranges. For example, we have :

1 3 4 6 8 9 10 14 15 19 20 22

Imagine we have this on the first column of an Excel sheet. What we want on the second column is:

1 3-4 6 8-10 14-15 19-20 22


Sound dead simple right? Ok, maybe you can skip reading this then. While its sound simple, it took me 15 minutes after a cup of 7-11 Slurpee Brainfreeze to write this :

Option Explicit

Sub Rangemaker()

     

    Dim i As Long

    Dim col As Long

    Dim sRow As Long

    Dim wRow As Long

    Dim seq As Boolean

    Dim LastRow As Long

     

    '  speed

    Application.ScreenUpdating = False

     

    '***begin change***

    '

    'init start row: CHANGE TO SUIT

    sRow = 1

    'set column to put results on: CHANGE TO SUIT

    col = 2

    '

    '***end change***

     

     

    'get last row of data to process

    LastRow = Cells(65536, 1).End(xlUp).Row

     

    'init dest row

    seq = False

    wRow = 1

     

    'do all rows from sRow

    For i = sRow To LastRow

        'see if next row is continuous

        If CLng(Cells(i + 1, 1)) <> CLng(Cells(i, 1)) + 1 Then

            'Not Continous

            If (seq <> False) Then

                'is part of a seq

                Cells(wRow, col) = Cells(wRow, col) & " - " & Cells(i, 1)

                wRow = wRow + 1

                seq = False

            Else

                'is not a seq but single number

                Cells(wRow, col) = Cells(i, 1)

                wRow = wRow + 1

                seq = False

            End If

        Else

            'Continous

            If (seq <> True) Then

                Cells(wRow, col) = Cells(i, 1)

            End If

            seq = True

        End If

    Next i

    

   

    'reset

    Application.ScreenUpdating = True

     

End Sub

Lets run through this a bit. I had added in as much comments as possible. Basically, you can play with 2 variables for your need.

• sRow which is which row to start making the range.
• col which is which column the result will appears.

Basically, it checks through each row and the next row and see if they are in continuous sequence. If no, then check if its already part of a sequence, which you should end with a "- ", otherwise then its a single number. If its in continuous sequence, you set some variables and continue.

For those who like insult to injury, to do this in Perl, its simply :
@a= (1,3,4,6,8,9,10,14,15,19,20,22); 

print num2range(@a); 

sub num2range {  

local $_ = join ',' => @_;  

s/(?<!\d)(\d+)(?:,((??{$++1}))(?!\d))+/$1-$+/g;  

return $_; 

}

Original idea and post:

http://www.ozgrid.com/forum/showthread.php?t=72891

Perl code was found from:

http://www.perlmonks.org/?node_id=87538