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:
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