Expand range of numbers

[dmingle]

I have a situation that I need help with.
In cell “A2” I have the following “789-793”. I would like to expand the sequence of numbers anytime a “-“ is present by using the 789 as the start (in this example) of the sequence and then using 793 as the end (in this example) of the sequence. I would like for the output to show up in “B2” and I would like the output to be separated by commas. For example, “789, 790, 791, 792, 793”.
There are various grouping of numbers all located in column “A:A” so if using VBA I would like to have a loop until all output is created in “B:B”.
I appreciate your help in advance.

 

[iliauk]

Rick - very nice code! Especially when you try it yourself and end up with something twice as long D:

Dim strval As String
Dim strArray() As String
Dim strexp As Long
Dim strfill As String
Dim comstring() As String
Dim rng As Range
Dim cell As Range
Dim j As Long
 
Set rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
 
For Each cell In rng
    comstring = Split(cell, ",")
        For j = LBound(comstring) To UBound(comstring)
        strArray = Split(comstring(j), "-")
            If UBound(strArray) > 0 Then
                strexp = strArray(0)
                    If strfill = vbNullString Then
                        strfill = strexp
                    Else
                        strfill = strfill + ", " & strexp
                    End If
                        Do Until strexp = strArray(UBound(strArray))
                            strexp = strexp + 1
                            strfill = strfill & ", " & strexp
                        Loop
            Else
                    If j > 0 Then
                        strfill = strfill & ", " & Trim(strArray(0))
                    Else
                        strfill = cell
                    End If
            End If
        Next j
cell.Offset(, 1) = strfill
strfill = vbNullString
Next cell

 

[Rick Rothstein]

Rick - very nice code! Especially when you try it yourself and end up with something twice as long D:

Dim strval As String
Dim strArray() As String
Dim strexp As Long
Dim strfill As String
Dim comstring() As String
Dim rng As Range
Dim cell As Range
Dim j As Long
 
Set rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
 
For Each cell In rng
    comstring = Split(cell, ",")
        For j = LBound(comstring) To UBound(comstring)
        strArray = Split(comstring(j), "-")
            If UBound(strArray) > 0 Then
                strexp = strArray(0)
                    If strfill = vbNullString Then
                        strfill = strexp
                    Else
                        strfill = strfill + ", " & strexp
                    End If
                        Do Until strexp = strArray(UBound(strArray))
                            strexp = strexp + 1
                            strfill = strfill & ", " & strexp
                        Loop
            Else
                    If j > 0 Then
                        strfill = strfill & ", " & Trim(strArray(0))
                    Else
                        strfill = cell
                    End If
            End If
        Next j
cell.Offset(, 1) = strfill
strfill = vbNullString
Next cell

I am afraid your code may have to get longer as it misses doing three things the OP asked for at various places in the thread. First, the OP did not want the expanded range as a comma delimited list in a singe cell; rather, he wanted each number in the expanded range to be in its own cell and those cells should start at B1 and be filled downward from there. Second, the expanded list from each cell in Column A should be listed one under the the other. Third, leading zeroes should be preserved if the range is shown with leading zeroes.

As for the way you did list it, you might find the routine I posted in my mini-blog article here to be of interest as it handles increasing and decreasing ranges where the ranges can be numerical or contain text (so long as it end with a number)...

<!-- title / author block -->Generalized Series Expansions (e.g. AB5-AB9 becomes AB5, AB6, AB7, AB8, AB9)

 

[darrow]

Hi Rick, im trying to understand your code and i'm commenting where i think needed for my own learning, could you tell me if my understanding is right? Im having problems understanding what exaclty the bolded parts are doing:

Sub ExpandSeries()  'variable declarations
  Dim X As Long, CG As Variant, Delim As String, Series As String
  Dim CommaGroups As Variant, DashGroups() As String, Ser() As String
  'Commagroups will be column A turned into  a row
  CommaGroups = Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
  'A CG is a each cell in in column A
  For Each CG In CommaGroups
	'If the range has a leading 0
    If Left(CG, 1) = "0" Then
      Delim = ",'"
    Else
      Delim = ","
    End If
	'remove all spaces in each cell in column A
    CG = Replace(CG, " ", "")
	'Seperate the upper and lower bound ine range but taking numbers without the dash
    DashGroups = Split(CG, "-")
	'for each of the numbers in the range
	'make Series string as the delimeter, 
    For X = DashGroups(0) To DashGroups(UBound(DashGroups))
[B]      Series = Series & Delim & Format(X, String(Len(DashGroups(0)), "0"))[/B]
    Next
  Next
[B]  Ser = Split(Mid(Series, 2), ",")[/B]
[B]  Range("B1").Resize(UBound(Ser) + 1).Value = Application.Transpose(Ser)[/B]
End Sub

Whats the purpose of the string or format functions? like how do they work? googling just confused me haha.

 

[pashof]

Hi Rick, im trying to understand your code and i'm commenting where i think needed for my own learning, could you tell me if my understanding is right? Im having problems understanding what exaclty the bolded parts are doing:



Whats the purpose of the string or format functions? like how do they work? googling just confused me haha.

Hi darrow,
Better late then never, I reverse engineered the code a bit further as I wanted to understand why this short masterpiece was indeed working:

Sub ExpandSeries()  'variable declarations
  Dim X As Long, CG As Variant, Delim As String, Series As String
  Dim CommaGroups As Variant, DashGroups() As String, Ser() As String
[I]  'Commagroups will be column A turned into  a row[/I]
  CommaGroups = Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
 [I] 'A CG is each cell in in column A, should be either just a number or a range[/I]
  For Each CG In CommaGroups
   [I] 'If the range has a leading 0 then it's not a number but a string, so add a '. Delim is used to delimit the numbers in the Series list below[/I]
    If Left(CG, 1) = "0" Then
      Delim = ",'"
    Else
      Delim = ","
    End If
    [I]'remove all spaces in each cell in column A, on request of the OP spaces should be excluded/not turned into 0's [/I]
    CG = Replace(CG, " ", "")
   [I] 'Seperate the upper and lower bound in the range but taking numbers without the dash[/I]
    DashGroups = Split(CG, "-")
   [I] 'for the first number in the range to the last number in the range (try 100-110-105 as range, it will produce 100, 101, 102, 104, 105)
    'add the Delim character and X correctly formatted  to the Series string [/I]
    For X = DashGroups(0) To DashGroups(UBound(DashGroups))
[B]     Series = Series & Delim & Format(X, String(Len(DashGroups(0)), "0"))[/B]
    Next
  Next
[I] 'Split the Series, not just Series, but MID(Series,2) which means Series minus the first character. The first character of Series needs to be removed because it's a comma (Series was empty, then Series = "" & Delim & X which would be ", 100")[/I]
[B] Ser = Split(Mid(Series, 2), ",")
[/B] [I]'Put all the numbers in B(1) to B(Arraylength+1)[/I]
[B] Range("B1").Resize(UBound(Ser) + 1).Value = Application.Transpose(Ser)[/B]
End Sub