Macro 40: Find and Select the First Blank Row or Column
You may often run across scenarios where you have to append rows or columns to an existing data set.
When you need to append rows, you will need to be able to find the last used row and then move down
to the next empty cell. Likewise, in situations where you need to append columns, you need to be able to
find the last used column and then move over the next empty cell. The macros in this section allow you to
dynamically find and select the first blank row or column. They are meant to be used in conjunction with
other macros. After all, these macros simply find and select the first blank row or column.
How it works
These macros both use the Cells item and the Offset property as key navigation tools.
The Cells item belongs to the Range object. It gives us an extremely handy way of selecting ranges
through code. It requires only relative row and column positions as parameters. Cells(5,4) trans-
lates to row 5, column 4 (or Cell D5). Cells(16, 4) translates to row 16, column 4 (or cell D16).
In addition to passing hard numbers to the Cells item, you can also pass expressions.
Cells(Rows.Count, 1) is the same as selecting the last row in the spreadsheet and the first col-
umn in the spreadsheet. In Excel 2010, that essentially translates to cell A1048576.
Cells(1, Columns.Count) is the same as selecting the first row in the spreadsheet and the last
column in the spreadsheet. In Excel 2010, that translates to cell XFD1.
Combining the Cells statement with the End property allows you to jump to the last used row or
column. This statement is equivalent to going to cell A1048576 and pressing Ctrl+Shift+Up Arrow on
the keyboard. When you run this, Excel automatically jumps to the last used row in column A.
Cells(Rows.Count, 1).End(xlUp).Select
Running this statement is equivalent to going to cell XFD1 and pressing Ctrl+Shift+Left Arrow on the
keyboard. This gets you to the last used column in row 1.
Cells(1, Columns.Count).End(xlToLeft).Select
When you get to the last used row or column, you can use the Offset property to move down or
over to the next blank row or column.
The Offset property uses a row and column index to specify a changing base point.
For example, this statement selects cell A2 because the row index in the offset is moving the row
base point by one:
Range(“A1”).Offset(1, 0).Select
This statement selects cell C4 because the row and column indexes move the base point by three rows
and two columns:
Range(“A1”).Offset(3, 2).Select
Pulling all these concepts together, we can create a macro that selects the first blank row or column.
This macro selects the first blank row.
Sub Macro40a()
‘Step 1: Declare Your Variables.
Dim LastRow As Long
‘Step 2: Capture the last used row number.
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
‘Step 3: Select the next row down
Cells(LastRow, 1).Offset(1, 0).Select
End Sub
1. Step 1 first declares a Long Integer variable called LastRow to hold the row number of
the last used row.
2. In Step 2, we capture the last used row by starting at the very last row in the worksheet and using
the End property to jump up to the first non-empty cell (the equivalent of going to cell A1048576
and pressing Ctrl+Shift+Up Arrow on the keyboard).
3. In this step, we use the Offset property to move down one row and select the first blank
cell in column A.
This macro selects the first blank column:
Sub Macro40b()
‘Step 1: Declare Your Variables.
Dim LastColumn As Long
‘Step 2: Capture the last used column number.
LastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
‘Step 3: Select the next column over
Cells(5, LastColumn).Offset(0, 1).Select
End Sub
1. We first declare a Long Integer variable called LastColumn to hold the column num-
ber of the last used column.
2. In Step 2, we capture the last used column by starting at the very last column in the work- sheet
and using the End property to jump up to the first non-empty column (the equivalent of going
to cell XFD5 and pressing Ctrl+Shift+Left Arrow on the keyboard).
3. In this step, we use the Offset property to move over one column and select the first blank
column in row 5.
How to use it
You can implement these macros by pasting them into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
