Recently, I complained about having trouble getting Excel VBA to correctly find the last row with data in a column, when using Excel tables. Thanks for the suggestions on solving that problem!
Jim Cone was intrigued by the find last row challenge, and from previous Excel adventures, Jim knew that Excel tables aren't the only obstacle to finding the last row.
Jim decided to write a universal LastRow function for a...
- Range (selection)
- Specific column – in a worksheet or range.
- Filtered data
For example, the LastRow function shows that row 40 has the last value in column E, even though that row is hidden by a filter.
How to Find the Last Row
Covered with dust, and bits of Excel VBA code, Jim has finally emerged from his basement workshop. He sent me his Find Last Row sample workbook, which you can download at the link below.
Here's what Jim had to say about writing the code:
- The (A) Find function and (B) Iteration of each column have advantages and disadvantages.
- The find function is faster, works on List/Tables but fails on a filtered range and occasionally throws up an unexpected error.
- The iteration method seems to be generally reliable but fails on both Lists/Tables and filtered ranges.
- In the past, I've used different pieces of code to find the last row as different circumstances dictate, but finally decided to put together a one size fits all answer. The following code is what I came up with.
- Note that it is actually two functions (both are required).
- Use of the second function avoids a compile error in Excel versions prior to the 2003 version.
- The approach is to search using find and switch to iteration if an error occurs.
Download the Sample Workbook
To see Jim Cone's Excel VBA code, you can download the Find Last Row in a Column sample workbook. The file is in Excel 2003 format, and zipped. Enable macros when opening the file, if you want to test the code.
Jim has added a few Excel tables to the worksheet, which you'll see in Excel 2007 and Excel 2010. In Excel 2003, those will appear as lists.
Click the button on the worksheet, to find the last row with data in the current selection. Or, you can modify the "DoesItWork" sub, so it will find the last row on a worksheet or specific column.