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
- The Sort Function
- Decide Which Range to Sort
- Apply the Sort Function
- Final word
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.
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:
Scary-long Intellisense for
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.
Keysare which data to sort on
Ordersare ascending or descending
Headerincludes or excludes the first row
OrderCustomuses a separate data structure that stores your own sort criteria (like days of the week M, T, W, Th…)
Orientationdefaults to Rows, meaning rows “move” up and down. Set to
xlSortColumnsto “move” columns left and right
SortMethodis only applicable to Hanzi (Chinese characters)
DataOptionscorrespond to their respective
Keysand default to treating text and numeric data separately. Set to
xlSortTextAsNumbersto 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:
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:
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
Columns.Sortif you only have sortable data in the columns and do not know exactly where it ends
Range.Sortif 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:
- We've decided we need to sort by
- We've determined our data has headers
- We don't have any data that shouldn't be sorted
- 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
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
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
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
xlDescending for the
We can sort columns A through E all together, so let’s specify the Columns collection as
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:
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 to your email address below.
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 to your email address below.
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.
A very easy way to see how the default header detection algorithm,
xlGuess, fails is to try this code:
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.
.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: