Excel Training VBA Training

How do I sort in Excel VBA?

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.

excel vba sort

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.

Read about our Excel VBA training solutions

By Richard Bailey

I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.