VBA: How to get a Range of Numbers from a List

So I’ve finally reached that point where I’m writing Macros well enough to accomplish simple tasks, but the results are just not pretty to look at. One of my macros returns a list of numbers associated with some data in a worksheet that looks like this:

, 1, 2, 3, 4, 5, 7, 10, 11, 12, 34, 35, 36, 37

Kind of gross, right? I want a to have a concise list. And DEAR GOD, you’d think writing the logic to get 1-5, 7, 10-12, 34-37 would be breeze.

Nope. I spent the last two days being bitch slapped with VBA errors.

So in the end, I stole the meat from this script posted by a wonderful human being named John Coleman. Coleman’s VBA is set up to run on a range of cells.

Only difference is that I want to be able to convert anything in my worksheet into a range in a separate cell, so this needs to be a User Defined Function. Since my numbers are in the same cell, they need to be delineated by commas. Luckily my Macros is insanely comma-happy. It puts them everywhere. Sometimes quite unnecessarily at the beginning of a list of numbers, so I need to modify the function to make it work both with and without a leading comma. Also, VBA is really nit-picky about data types. My number list is saved as a string. The first hurdle is just getting Excel to recognize those as integers.

Step 1: Split a String into a String Array

You can take a string and parse it into an array by using Split(). As written below, it’ll take the string textString, find the commas, and chop it up there.

numberArray() = Split(textString, ", ")

To get the lowest and highest index of an array, you can use LBound and UBound. This is really useful for running loops through an array.

Step 2: Convert That String Array into an Integer Array

Say our string array is called numberArray(). You could set a For Loop to run through each index to the highest one and run CInt() to turn the values into integers. Then stash them into an integer array for later reference. Like so:

Dim numberArray() As String
Dim intArray() As Integer, size As Integer, a As Integer
size = UBound(numberArray)
ReDim numbers(size)
For a = 1 To UBound(numberArray)
intArray(a) = CInt(numberArray(a))
Next a

I know what you’re thinking. What’s that ReDim line for? You can use that to change the size of an array—in this case, we’re changing the integer array (intArray) to be the same size as the string array (numberArray).

You may also be wondering why the conversion is necessary at all? The meat of the script needs to do addition and subtraction on the values. Anytime you need to run math on numbers, they need to be available as some sort of number data type (integers, floats, doubles).

Step 3: Fix the Leading Comma Problem

When I started writing this, I got it to work for cells that start with a comma and a space. Like so.

Then I could not, for the life of me, figure out why it wouldn’t work on a list that doesn’t start with a comma (notice how C3 is missing the number 1?). The Split() function is meant to take the value before the first delimiter too. So I started looping through my list of 10 Most Frequent Lapses In Coding Logic. And I’m pretty sure this is because of #1—Arrays start counting at 0. Meaning when I split an array that start with a comma, it  doesn’t store anything at Index 0. But when I split an array that doesn’t have a comma, it stores the first number at Index 0.

And I, not being the sharpest tool, started my conversion loop at a=1. It ignores anything stored at the first index (0).

And I, being lazy, decided that fixing the underlying logic problem would be harder than just adding a comma to the beginning of the string if it doesn’t already have one. That’s why these lines are there:

If Left(textString, 2) <> ", " Then
textString = ", " & textString
End If

Step 4: Apply The Logic

The IIf line is really key in here. Essentially what that does is tell Excel that if the starting number within the range is less than the ending number, we want it to push both the start and end numbers into the string with a dash. If the start and end number are the same, we need it to just add the end number once with a comma to start off the new range.

The syntax for IIf is like this:

IIf(Expression, Return Value if True, Return Value if False)

You can see that in action here:

The IIf helps Excel avoid duplication for number 7, which is a range all on its own. Otherwise, VBA would return 7-7.

The loop is set to run 13 times in the gif because the highest index is 13. That, however, terminates before the addition of the final range.

This works out well though, because if it ran all the way through, we’d have a trailing comma. Better to just add in one last line after it’s all said and done to process the final range.

LstRng = IIf(startNum < endNum, startNum & "-" & endNum, endNum)
strg = strg & LstRng

And that’s about it. Full script is below to be plugged into a module for use inside a spreadsheet.

The Code

Function ListToRange(textString As String)

'THIS IS TO CONDENSE THE NUMBERS
Dim numberArray() As String
Dim strg As String
Dim numbers() As Integer, size As Integer, a As Integer
Dim newstrng As String

If Left(textString, 2) <> ", " Then
textString = ", " & textString
End If

numberArray() = Split(textString, ", ")
size = UBound(numberArray)
ReDim numbers(size)
For a = 1 To UBound(numberArray)
numbers(a) = CInt(numberArray(a))
Next a

Dim startNum As Integer
Dim endNum As Integer
Dim Value As String
Dim LstRng As String

startNum = CStr(numbers(1))
endNum = startNum
For X = 2 To UBound(numbers)
If numbers(X) <= endNum + 1 Then
endNum = numbers(X)
Else
Value = IIf(startNum < endNum, startNum & "-" & endNum & ", ", endNum & ", ")
strg = strg & Value
startNum = numbers(X)
endNum = startNum
End If
Next

'To complete the last range
LstRng = IIf(startNum < endNum, startNum & "-" & endNum, endNum)
strg = strg & LstRng
ListToRange = strg

End Function