Converting a mixed list of numbers and number ranges into a single list in a new column

[That_Guy86]

Hello-

I'm attempting to take a list of numbers that can be single numbers (123456) or a range separated by a hyphen (123457-123500) contained in different cells within 1 column and have it produce a list of every number in another column.

For example:

123456
123457
123458
123459
123460
..
123498
123499
123500

I'm attempting to automate this process as much as possible.

Thanks in advance.

 

[rlv01]

Welcome to MrExcel. Perhaps something like this.

Sub ExpandList()
    Dim WS As Worksheet
    Dim rng As Range
    Dim R As Range
    Dim I As Long
    Dim Ofs As Long
    Dim VA As Variant
    
    Set WS = ActiveSheet

    With WS
        Set rng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
        .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).ClearContents
    End With

    Ofs = 0

    For Each R In rng
        VA = Split(R.Value, "-")
        Select Case UBound(VA)
        Case 0
            rng.Range("A1").Offset(Ofs, 1).Value = VA(0)
            Ofs = Ofs + 1
        Case 1
            For I = VA(0) To VA(1)
                rng.Range("A1").Offset(Ofs, 1).Value = I
                Ofs = Ofs + 1
            Next I
        Case Else
        End Select
    Next R
End Sub

Book2
	A	B
1	List	Expanded List
2	123456	123456
3	123457-123470	123457
4	800	123458
5	995	123459
6	996-1007	123460
7		123461
8		123462
9		123463
10		123464
11		123465
12		123466
13		123467
14		123468
15		123469
16		123470
17		800
18		995
19		996
20		997
21		998
22		999
23		1000
24		1001
25		1002
26		1003
27		1004
28		1005
29		1006
30		1007
Sheet6

 

[Peter_SSs]

Welcome to the MrExcel board!

Especially if the final list might be a long one, this should be quicker. I've assumed data in A2 down and results in column B.
Test with a copy of your workbook.

Sub ExpandList_v2()
  Dim a As Variant, b As Variant, Bits As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For i = 1 To UBound(a)
    Bits = Split(a(i, 1) & "-" & a(i, 1), "-")
    For j = Bits(0) To Bits(1)
      k = k + 1
      b(k, 1) = j
    Next j
  Next i
  Range("B2").Resize(k).Value = b
End Sub