Thread: Problem comparing items in (2-dim?) array created from range

Hi all. I've made a Sub that goes through a list of sorted numbers, and checks for continous ranges, like this: 1, 2, 3, 4, 5, 6
I'd like to collapse these ranges to this format: 1-6.
So i check if the next number is one bigger than the previous number. If it's not, I build a string of the first and last number of a continous range, with a dash in the middle, and a comma and a space at the end, like this: 1-6,
Then i write the next number to a temporary string and repeats.
I got this working good, when I hardcoded an array

MyArray(0) = 1
MyArray(1) = 2

and so on.

However, in the real world, these numbers are in a column on a sheet, and when i use:

Dim MyArray As Variant
MyArray = Range("some:range").Value,

a twodimensional array seems to be created, so i have to access it's items by writing MyArray(i, 1). When doing this I get a problem when trying to compare the value of one item in the array with the previous.

Here follows the code for the harcoded version, and then the one where the array is populated from a range:

Sub CollapseList()

    Dim i As Long
    Dim sTemp As String
    Dim sOutput As String
          
    Dim MyArray(0 To 9)
    MyArray(0) = 1
    MyArray(1) = 2
    MyArray(2) = 3
    MyArray(3) = 6
    MyArray(4) = 7
    MyArray(5) = 8
    MyArray(6) = 14
    MyArray(7) = 15
    MyArray(8) = 16
    MyArray(9) = 22
    
    sOutput = MyArray(0)
    
    For i = 1 To UBound(MyArray)
        If MyArray(i) = (MyArray(i - 1) + 1) Then
            sTemp = MyArray(i)
        Else
            sOutput = sOutput & "-" & sTemp & ", " & MyArray(i)
        End If
    Next i
    Debug.Print (sOutput)
End Sub

The above example works fine, and outputs : 1-3, 6-8, 14-16, 22

Sub CollapseList2()

    Dim i As Long
    Dim sTemp As String
    Dim sOutput As String
          
    Dim MyArray As Variant
    
    MyArray = Range("E2:E17").Value

    sOutput = MyArray(1, 1)
    
    For i = 2 To UBound(MyArray)
        If MyArray(i, 1) = (MyArray((i - 1), 1) + 1) Then
            sTemp = MyArray(i, 1)
        Else
            sOutput = sOutput & "-" & sTemp & ", " & MyArray(i, 1)
        End If
    Next i
    Debug.Print (sOutput)
End Sub

This I can't get working. Something I have not understood about arrays I guess.
I left my Excel VBA Power Programming book at work, and is trying to work this out from my home office, so any help is appreciated.
My plan, after getting the comparison thing working, is to generalize this further and make a function so it can be used in and excel cell by anyone at the office.

Hope anyone see the obvious mistake I'm making here and points it out to me

Many thanks in advance.

Regards,

Tom

Hi Tom

I am not really following your post. Nevertheless, based on your first example
I put the numbers 1,2,3,6,7,8,14,15,16,22 into column E and used

MyArray = Range("E2:E11").Value ( versus your code in the 2nd example )
MyArray = Range("E2:E17").Value

and the output from the 2nd routine was the same as the output from the
1st routine.

That isdebugger)

1-3, 6-8, 14-16, 22


FWIW
regards
John

I amended the range to end at E11 to fit with your first example and the second bit of code worked for me.

Hi people.
It did work, when i formatted the cells to Number format.
The range was a product of a len() and right() funtion to strip some text, and that left the cell formatted as General, with the green triangle.
Thanks for testing and confirming, I was certain there was something wrong with the routine.

snb: I will try to read and understand your code some time during the day.
It took me some thinking and trial to make this, my first real function ever, so i guess it's not the most beautiful

Good guys, fine forum.

Actually, it didn't work inn all cases. Figured I had to test it some, and when one number not part of any continous range appeared in the middle somewhere, it caused problems.
So I figured i had to put in another test, and an incrementor to check how many times the if-statement had run. Anyways, here's the code for collapsing a range of numbers!
Someone in the office is going to save lots of time now, thanks to my persistence.
Worked a long time trying to learn to program, but now it feels like I'm beginning to think right. Please, feel free to point out any bad habits in this example, as I'm all fresh but genuinely interested and willing to learn.

Sub CollapseList2()

    Dim i As Long
    Dim j As Long
    Dim lSingle As Long
    Dim sTemp As String
    Dim sOutput As String
          
    Dim MyArray As Variant
    
    MyArray = Range("E2:E13").Value
    
    j = 0
    sOutput = MyArray(1, 1)
    
    For i = 2 To UBound(MyArray)
        If MyArray(i, 1) = (MyArray((i - 1), 1) + 1) Then
            sTemp = MyArray(i, 1)
            j = j + 1
 
        Else
            If j = 0 Then
                lSingle = MyArray(i, 1)
                sOutput = sOutput & ", " & lSingle
            Else
                sOutput = sOutput & "-" & sTemp & ", " & MyArray(i, 1)
                j = 0
            End If
        End If
    Next i
    Debug.Print (sOutput)
End Sub

I guess next step is to make this sub into a function, that lets me type =Collapse in a cell, and then lets me select a range. Then write the output to a cell.

Here are some keywords for people trying to search the internet for this type of code, as I did:
collapse continous range boundaries function excel vba

Thanks for posting your solution. Looks good. You've done the hard work, turning it into a function will be the easy bit now.