May 15, 2014

When creating macros in Microsoft Excel, you often want to find the last row of data in a worksheet to be able to choose the correct area so the macro can do things with that area. The main challenge here is that each worksheet you run your macro is almost certainly is going to have different amounts of data. A lot of the time you can’t just record a macro, to say perform a Sort, and expect it to work for data of differing sizes because the VBA code ‘locks in’ the area. For example, here is my recorded code performing a Sort (A to Z, by column A):

Notice the range that this macro will sort is ‘locked in.’ I have underlined the ‘locked in’ bits in red. There are certain keyboard shortcuts you can use to help with this. In this post, I'll go through one example with one particular keyboard shortcut and in my next post, I'll discuss 3 more shortcuts.

Using control arrows to find the last row of a worksheet

If you start the selection at the top of a list of data and click Ctrl+Down Arrow on your keyboard, the selection moves to the other end of the area with data in it. So if you start on a blank cell, you will move to the next non-blank cell in that direction (or to the end of the sheet if there is nothing in that direction). If you start on a non-blank cell, you will move to the other end of the non-blank cells. Try it out on one of your lists and see how it works. The only problem is that it stops if there are gaps (i.e. blank cells in the middle of your data). Sometimes with a macro you have to make assumptions, but as long as you can justify these assumptions then this isn't terrible. With control arrows, if you know that there is a column in your data that always has data in it (no blank cells in the middle), then you can assume that by starting with the selection at the top of that row and clicking Ctrl+Down Arrow, you will end up at the bottom of the list. Let’s record doing this and see what it looks like: By looking at these two lines, you can see that the first line is where I went to cell K4 and the other line is the Ctrl+Down Arrow action. Notice that it doesn't lock in how far down to move, which is really good. How do we find out the current row number we are on? VBA has many properties such as the '.Address property' and the '.Row property.' If you go into the immediate window and try these two lines:

?Selection.Address ?Selection.Row

You will see that .Address returns the selection’s range with $ signs on it, for example, $E$15:$H$25, while .Row returns the top row of the selection. Now, how do we store that information so that we can use it later in the code? To do this, we will store it in a variable. Why don’t we just use the property directly? This is because the selection is at the bottom of the list now, but it might not be still there later on in the code. There is a lot more to variables, but to keep it simple, a variable is like a cup that holds information. It’s a plastic cup because the cup gets thrown away when the macro has finished running, but it can certainly be used while the macro is running. I’m going to store the information that 'Selection.Row' gives us in a variable called 'LastRow.' Now LastRow is only a name I made up, it’s not an official VBA Excel name (you don’t want a variable name to be the same as a real official VBA name because that would confuse the VBA editor a lot!) So I add a line to the code: Now I can use the information stored in LastRow later on in the macro. Copy and paste that code to Sort (which I showed you earlier) and replace the two '98's with what is stored in LastRow by concatenating it on the end at those two places as seen below.

Here’s a copy of the code to copy:

Sub FlexibleSort() Range("K4").Select Selection.End(xlDown).Select LastRow = Selection.Row ActiveWorkbook.Worksheets("Payroll").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Payroll").Sort.SortFields.Add Key:=Range("A5:A" & LastRow) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Payroll").Sort .SetRange Range("A4:K" & LastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub

So now the macro won’t sort only a fixed number of rows, it is flexible in the number of rows that it sorts. I hope the concept makes sense; use this keyboard shortcut technique to move the selection to the bottom of the list, take note of what the bottom row is and store it in a variable, then use that variable later in the macro. This is only 1 of 4 shortcuts that you can use to find the last row of a worksheet. In my next post, I'll go through the remaining 3 shortcuts, so stay tuned!