Finding the Last Row in a Column

There are several ways to find the last used row (or column) in a spreadsheet.

There are the manual methods:  Assuming we want to position the active cell on the last row within a column. Begin by clicking on the first non-blank cell, then choose one of these methods:

• Ctrl + ↓ (down arrow key)
• Ctrl + End key

Note: By adding the Shift key after the Ctrl key in the above examples, the range from the active cell to the last row cell will be selected:

• Ctrl + Shift + ↓ (down arrow key)
• Ctrl + Shift + End key

If we ask the macro recorder to capture either of the above first two key sequences, we can see that the VBA code generated will look like the following. Assume the top-most non-blank cell is C4:

Yes, there are multiple ways to achieve the same thing, but is there a difference? Actually, yes. What if your data looked like the following? Notice that there is a blank cell, C7:

If we run the first key sequence, Ctrl + ↓ (down arrow key) (or run the equivalent VBA code), the last cell found will be C6, however, running the second key sequence, Ctrl + End key, the last cell found will be C12. The first option searches cells downward until it finds a blank cell, while the second option checks all cells in that column before determining the last used cell.

Via VBA, there are a few ways to find that last cell. Assume in our example that we want to locate the last-used cell, regardless of our starting (active) cell. For that, we can refer to the ActiveSheet as our starting point (Note bullets 2 and 4 are variations on what was described above):

• Cells.Find(“*”,searchorder:=xlByRows, searchdirection:=xlPrevious).Select
• Cells.SpecialCells(xlCellTypeLastCell).Select
• Cells(ActiveSheet.Rows.Count, “C”).End(xlUp).Select
• Cells(4, “C”).End(xlDown).Select
• UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Select

Here is a sample workbook that shows the various methods