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.

 

[MickG]

Try this:-

Dim rng As Range
Dim Dn As Range
Dim n As Integer
Dim num As Variant
Dim Txt As String
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    For Each Dn In rng
        num = Split(Dn, "-")
            For n = num(0) To num(1)
                Txt = Txt & n & ", "
            Next n
                Dn.Offset(, 1) = Left(Txt, Len(Txt) - 1)
                Txt = vbNullString
    Next Dn

 

[dmingle]

MickG:

I am impressed. That worked perfectly.

Do you have any recommendations for situation like this?:

"A1" has contents of "789-793, 798". The prefered outcome would be "789,790,791,792,793,798".

 

[dmingle]

Also these are other cases.

Possible situations are as follows:

Situation (1): 789-793
Situation (2): 789-793, 789
Situation (3): 789
Situation (4): Blank

Since I didn't tell you about all scenerio's in the first part of my post the code bombs when it encounters such events.

 

[MickG]

Try this:-

Dim rng As Range
Dim Dn As Range
Dim n As Integer
Dim num As Variant
Dim Txt As String
Dim fd As Boolean
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    For Each Dn In rng
       If Not Dn = vbNullString Then
        fd = False
        If InStr(Dn, ",") > 0 Then
            num = Split(Split(Dn, ",")(0), "-")
            fd = True
        Else
            num = Split(Dn, "-")
        End If
            If UBound(num) = 0 Then
            Txt = Dn
            Else
            For n = num(0) To num(1)
                Txt = Txt & n & ", "
            Next n
            End If
                If fd = False Then
                     If UBound(num) = 0 Then
                          Dn.Offset(, 1) = Dn
                      Else
                          Dn.Offset(, 1) = Left(Txt, Len(Txt) - 2)
                      End If
                Else
                    Dn.Offset(, 1) = Txt & Split(Dn, ",")(1)
                End If
                    Txt = vbNullString
    End If
    Next Dn

 

[dmingle]

Ridiculous, it works without a hitch.
5 Stars from me to you.

 

[Rick Rothstein]

Here is a shorter code routine which should also do what you want...

Dim X As Long, CG As Variant, Rng As Range, Cell As Range
Dim Series As String, CommaGroups() As String, DashGroups() As String
Set Rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
For Each Cell In Rng
  CommaGroups = Split(Cell, ",")
  For Each CG In CommaGroups
    DashGroups = Split(CG, "-")
    For X = DashGroups(0) To DashGroups(UBound(DashGroups))
      Series = Series & ", " & X
    Next
  Next
  Cell.Offset(, 1).Value = Mid(Series, 3)
 Series = ""
Next

 

[dmingle]

Rick:

Thank you for the follow-up code.

It works as well. The processing speed is faster as well, due to your routine being shorter.

Every little bit counts...

DM

 

[darrow]

edited post- I deleted original question because i realized my mistake, however i do have some questions on this topic which i will return with, sorry for bumping it up too early!

 

[darrow]

Here is a shorter code routine which should also do what you want...

Dim X As Long, CG As Variant, Rng As Range, Cell As Range
Dim Series As String, CommaGroups() As String, DashGroups() As String
Set Rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
For Each Cell In Rng
  CommaGroups = Split(Cell, ",")
  For Each CG In CommaGroups
    DashGroups = Split(CG, "-")
    For X = DashGroups(0) To DashGroups(UBound(DashGroups))
      Series = Series & ", " & X
    Next
  Next
  Cell.Offset(, 1).Value = Mid(Series, 3)
 Series = ""
Next

Hi, back with my actual question, i was wondering, how could i go about writing all the results in a single column instead of having them written in different rows how the script does?

and i noticed if the values have a leading zero the script ignores them, how could i go about allowing that?

I have a set of ranges as well, some starting with zeros, say all the ranges are listed in column A1, but i want the expanded ranges to be written all in a single row, example:

001-003
111-114
432-434

I'd like the output to show as

001
002
003
111
112
113
114
432
433
434

I was wondering if you could guide me, since im a VB/excel complete beginner.