Excel VBA is used for all sorts (!) of data manipulation tasks. One of the most common is sorting your data. Performing an Excel VBA sort seems trivial but some parts of it may misbehave if you’re not diligent.
I’ve seen some people try to write their own sort routine in Excel VBA, but frankly Microsoft has spent thousands (millions?) of dollars refining their Excel Sort technique, so why not just record a macro where you use that?
It starts simply enough. View > Macros > Record new macro, fill in the details, hit Sort then Stop Recording.
Play it back – seems fine.
Add some new rows, play it again – still fine.
But it isn’t.
The problem comes when you look at what has been sorted. If you notice, your macro has only worked on the same range as your original recorded macro. Extra data outside of that first selection will not be included.
A quick peek in the Visual Basic Editor (ALT + F11) will demonstrate this clearly.
Boom, there it is. A reference to a fixed range of cells (A2:H31). Aside from the other typically overcautious lines from the macro recorder (thank goodness the xlPinYin method was set!) these cell references are the bits that will cause you problems. It’s the worst kind of error too, the one that doesn’t cause an error but silently causes havoc in the background.
The key is then to create a dynamic range. This is the source of many issues with beginner code in VBA. As with most VBA problems, there are many ways to solve it. Try this out:
Sub MySortMacro() Dim LastRow As Long LastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _ Order1:=xlAscending, Header:=xlNo End Sub
Let’s take a look at that.
Dim LastRow As Long
Create a new variable called LastRow as a Long datatype (an integer that goes roughly up to 2 billion). Why a Long and not an Integer? An Integer goes up to ~36,000. If your data is 36,000+ rows, you will run into the overflow error. 2 billion is plenty!
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Count how many rows are in the first column (,1). Go to that last cell and do a CTRL + up arrow motion. In most sets of data this will hit the last item of data in that column. Get the row number then store that in the LastRow variable.
Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _ Order1:=xlAscending, Header:=xlNo
This heavily truncated command concatenates (or ‘joins’) the LastRow variable to the ranges required. The “A2:H” bit is the full range of data, “C3:C” is which column you want to sort by.
There you go. Simpler code, easier to read and will work with any range of data.
You can get lots of great VBA tips like this on our Excel VBA Introduction / Intermediate courses, or Excel VBA Advanced training course.