VBA: How to Get A List From a Range of Numbers

A couple months ago, I did the exact opposite of this. I called it the ListToRange() Function. It’s weirdly helpful at work because I have to go back and forth between having numbers listed out and then having them clustered for other things.

And it’s a real pain in the ass to have to keep doing it by hand. Every so often, I skip a number, and it throws my whole day off trying to figure out where the hell I went wrong. I’ve come to the conclusion that this is the sort of thing a computer should be doing, not a human.

So, here’s the RangeToList() Function. It takes a single string parameter—this can be a cell reference. Once you’ve got it plugged into a Module in your workbook, it’ll populate automatically in the formula drop-down menu. Like so:

The Script

Alright, to be honest, I used almost the exact same logic as a completely different function I wrote a while back called WorkAreaCount(). It’s job is to count values in a string range. Instead of counting though, I just had it append each individual number into a new string. That string is what the function returns.

Here’s the breakdown:

The Copy/Paste Version

Function RangeToList(full As String)

Dim finalList As String
Dim i As Integer
finalList = ""
full = full & ","

Do While Len(full) > 0
'While the full string still has characters…
full1 = Left(full, InStr(full, ","))
'full1 is equal to everything to the left of the comma (the range or number)
If InStr(full1, "-") > 0 Then
'If there's a dash in there, it's a range and we need to do the following…
posNum = InStr(full, "-")
'Find the position of the dash
StNum = Val(Left(full, posNum))
'Set StNum to the 1st number in the range (before the dash)
full = Right(full, Len(full) - posNum)
'full now equals everything to the right of the dash
posNum = InStr(full, ",")
'posNum now equals the place of the next comma
EndNum = Val(Left(full, posNum))
'EndNum now equals the last number of the range
full = Right(full, Len(full) - posNum)
'full now equals everything to the right of the range we just parsed
i = StNum
'Set i to Starting number of range
Do While i < EndNum + 1
'While i is less than the end number…
finalList = finalList & i & ","
'Add the values from the start number to the end number
i = i + 1
Loop
Else
'If there's no dash, this is what to do in case of a single number
finalList = finalList & full1
full = Right(full, Len(full) - InStr(full, ","))
'full now equals everything to the right of of the comma
If full = "," Then
full = ""
End If
End If
Loop

finalList = Left(finalList, Len(finalList) - 1)
‘Take away the last comma
RangeToList = finalList

End Function