Parsing Number sets to determine rows.

[mack5511]

I am working with an Excel sheet where I need to take numbers from a cell and then parse them to determine what rows to run.

An example of the data would be something like this: 2,5-10,20-27

I need to figure out how to build an array of the numbers to determine the correct rows to run.

So the result would be:

2 5 6 7 8 9 10 20 21 22 23 24 25 26 27

Any help would be appreciated. I did a search of the forum but no luck.

Mack

 

[kpark91]

Very time-consuming, inefficient code:

Option Base 0
Sub createArray()
    Dim str$, arr%(), i&, j&, count&, v1&, v2&
    Dim commaArr As Variant, dashArr As Variant
    
    str = "2,5-10,22-26"
    commaArr = Split(str, ",", , vbBinaryCompare)
    count = 0
    
    For i = LBound(commaArr) To UBound(commaArr)
        dashArr = Split(commaArr(i), "-", , vbBinaryCompare)
        v1 = dashArr(0)
        v2 = dashArr(0)
        
        If UBound(dashArr) > 0 Then
            v2 = dashArr(1)
        End If
        
        For j = v1 To v2
             ReDim Preserve arr(0 To count)
             arr(count) = j
             count = count + 1
        Next j

        
    Next i
    
End Sub

 

[Jonmo1]

Do you have any control over the source of the string?

Can 2,5-10,20-27 be changed to say
2:2,5:10,20:27

 

[Scott Huish]

You could very easily change that input in code.

mack, why an array, how about just a range that contains all the rows in your string?

Sub test()
Dim r As Range, s As String, rws, t As Long
s = "2,5-10,20-27"
s = Replace(s, "-", ":")
rws = Split(s, ",")
For t = LBound(rws) To UBound(rws)
    If r Is Nothing Then
        Set r = Rows(rws(t))
    Else
        Set r = Union(r, Rows(rws(t)))
    End If
Next
r.Select
End Sub

 

[mack5511]

I have control over the source of the input string. The array was just the first thing I was thinking. It could be a range. I am just feeding into another function to control what rows are read.

Thanks for the input everyone.

This definitely gets me started.

Mack

 

[Jonmo1]

If you can make the string like

"2:2,5:10,20:27"
Notice the 2:2

Sub test()
MyString = "2:2,5:10,20:27"For Each r In Range(MyString).Rows
    MsgBox r.Row
Next r
End Sub