Why do we use spreadsheets? One reason is organization. We like to organize our data so we can make intelligent calculations on it. That’s the essence of a spreadsheet. One of the most common ways to organize your data is by sorting, which happens to also hold the prestige of being a fundamental problem in computer science.

When people talk about “sorting columns” or “column sorting”, they are conflating two ideas: sorting full columns (Rows 1 to 65,000+) and sorting a range using a column as a key. People conflate the two ideas because they’re very similar. Due to their similarity, we’ll cover both.

For this tutorial, we’ll use a business-oriented example data set. Naturally, you can use whatever dataset you have. As long as the data comes in columns and rows and you want to sort it, this tutorial is for you.

The Example Data

You run a business and have an Accounts Receivable sheet for the day’s orders made on credit. The orders are added as the day goes by, so they are in ascending chronological order. Before committing this sheet to your database - and you do use a database, I hope - you like to check the amounts owed and from which cities most orders came. You’re old-school, so you just like to eyeball these numbers, but you need to sort the data first.

If you want to practice, you can download this CSV file of the sample data and follow along. If you’d rather just use the image, check out the screenshot.

Screenshot of sample data
Sample Data

Do you recognize any names?

The Sort Function

Yes, we know we just called it a function, but .Sort is really a method. The .Sort method in VBA is pretty straightforward, though it can seem daunting at first if you have Intellisense turned on:

Intellisense for Sort Function
Scary-long Intellisense for Sort

You don’t need to use all these parameters, though. We’ll only look at keys, orders, and headers. You can play with the others if you’d like. For better guidance in your explorations, these bullets very briefly explain what they other parameters do.

  • Keys are which data to sort on
  • Orders are ascending or descending
  • Header includes or excludes the first row
  • OrderCustom uses a separate data structure that stores your own sort criteria (like days of the week M, T, W, Th…)
  • Orientation defaults to Rows, meaning rows “move” up and down. Set to xlSortColumns to “move” columns left and right
  • SortMethod is only applicable to Hanzi (Chinese characters)
  • DataOptions correspond to their respective Keys and default to treating text and numeric data separately. Set to xlSortTextAsNumbers to mix them

Decide Which Range to Sort

Normally, people want to sort entire columns (rows 1 to the end). This can be achieved by using a subset of the Columns collection. This code block will sort all data in Columns A, B, C, and D using default sorting options:

Columns("A:D").Sort

Sometimes, you may have formulas or other information in rows after your table of data. In that case, you’d want to sort on a “shorter” range, say rows 1 to 50. From Row 51, maybe you have another set of data that should not be sorted. Alternatively, maybe you have analytics at the top of the sheet above the data that should be sorted. For example, perhaps the sortable data only starts at row 20. In that case, you’d use a range object. This snippet will only sort the square block from cell A20 to cell D100, leaving your analytics that are entered in A1 to D19 untouched:

Range("A20:D100").Sort

You can use the Range approach and cover a large area, too, like Row 20 down to Row 20,000. Here are some general rules of thumb for using .Sort:

  • Use Columns.Sort if you only have sortable data in the columns and do not know exactly where it ends
  • Use Range.Sort if you have unsortable data in the same columns as sortable data.

Apply the Sort Function

Now that you’ve decided the proper range, let’s get sorting! First, we must assess our needs for our hypothetical business situation:

  1. We've decided we need to sort by City and Amount
  2. We've determined our data has headers
  3. We don't have any data that shouldn't be sorted
  4. We don't know how many orders we'll have each day

Points 3 and 4 mean we can use the Columns collection, and in fact we should, since we don’t know how many orders will come in a day. If we use the range, we might accidentally miss orders that fall outside the range.

Point 2 means we should set the Header parameter to xlYes or xlGuess. The latter lets Excel guess whether or not headers exist. Since we already know we have headers, it’s better to be explicit and set Header to xlYes rather than relying on the logic of Excel’s guessing algorithm.

Since cities are duplicated, we want to sort first on cities, then on amounts. This approach will gather all of the cities together, ordered alphabetically, then sort by the amount within each city group. What we’re left with are ordered amounts, grouped by city.

So, for Point 1, we want to use the City column as the first key and Amount as the second key. For SortOrder, xlDescending corresponds to 9 → 0 and Z → A order. Normally we would use A → Z for cities. For amounts, we want the biggest at the top of its City group, so let’s use xlDescending for the Amount and xlAscending for City.

We can sort columns A through E all together, so let’s specify the Columns collection as Columns("A:E").

All together, it looks like this:

Sub SortData()
Columns.Sort key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("E"), Order2:=xlDescending, Header:=xlYes
End Sub

Phew! That’s a long VBA expression, but it all works out as we expected:

Screenshot of sample data sorted
Sample data sorted by City (A-Z) and Amount (9-0)

The red box shows the A → Z sorting of cities, while the blue and green boxes highlight descending amounts in Annapolis and Baltmore respectively.

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

Do we need to specify which columns?

In the code above, we used Columns("A:E") to specify we wanted to sorted only columns A, B, C, D, and E. If you simply use the Columns collection, all columns on the sheet will be sorted. If you’re uncertain whether there’s data that shouldn’t be sorted somewhere on your spreadsheet, I recommend explicitly specifying the columns to sort. Sometimes spreadsheet creators store static data in Column AA or a similar column far off to the right. If you use the entire Columns collection, without specifying which columns, this static data will be rearranged too.

When xlGuess Fails

A very easy way to see how the default header detection algorithm, xlGuess, fails is to try this code:

Columns.Sort Columns("A")

Header sorted with data
xlGuess guessed incorrectly

The data is sorted by the Family Name Key in Column A, but since Excel didn’t guess correctly about headers, the header was sorted as if it were also a name. This is very common when your header data type, String in this case, matches the data type of the data in the column.

Final word

Use .Sort on a Range object or Columns collection to sort data based on the columns in question. A macro for sorting can be as simple as specifying a single Column as a key (Columns.Sort Columns(1)) to as complex as layering keys for groups (group Cities then sort Amounts descending) while narrowing the data by calling out specific ranges (Columns("A:C").Sort). If you start to get into custom orders and orientation, it can be even more complicated.

You need to assess the situation and choose which parameters to include and how to set up the code for your use case. After all, that’s what programming is all about.

If you were wondering: after architect Walter Gropius, the names are the signatories of the United States Declaration of Independence.

If you haven’t already done so, I encourage you to subscribe below for a structured curriculum designed to help you learn more VBA tricks like this one. We use this curriculum to guide you through more advanced sorting algorithms, like these custom algorithms for sorting data inside an array: