Find Last Row With Excel VBA

image 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...

  1. Worksheet
  2. Range (selection)
  3. Specific column – in a worksheet or range.
  4. List/Table
  5. 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.

FindLastRow01

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.

 

Thanks Jim!

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.

________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

8 comments to Find Last Row With Excel VBA

  • Eric

    I downloaded the file and tried it out. I selected three cells, E39:E41, and the answer was 40. E41 had no information in it... but I was expecting the answer 41.

    I selected column 5 (header "Five") of the table... the answer was 25 instead of 27. Again, no data in rows 26 or 27, but I expected the answer 27.

    For both of my examples, the problem is the use of End(xlUp). This method will work well if there is guaranteed data in the cells. I am a big non-fan of End(xlUp).

    This, however, seems to work for contiguous (or at least rectangularly contiguous) selections:

    Selection.Columns(1).Cells(Selection.Rows.Count).Row

  • Thanks Eric, and Jim's code is designed to find the last row with data in the selection. That might be above the last used row on the worksheet, or in a named table.

    I've added "with data" in the blog post, to make that clearer.

  • Eric

    It just goes to show you that the last row isn't necessarily the last row, and a universal function will only work for the universe that you intend. Your users will be very glad to show you how your intended universe is not the one they live in.

  • So true! And I wish that all users could give feedback as clearly as you did, instead of saying, "It didn't work!" ;-)

  • Rick Rothstein (MVP - Excel)

    @Debra (and everyone else as well),

    Below are two functions that are loosely related to your blog article's subject that I thought you and your readers might find of interest... one returns the top visible filtered row number for the active AutoFilter and the other returns the bottom visible filtered row number. You do not have to select any ranges for these functions to work... simply activate the AutoFilter and then run your code that uses them.


    Function GetFilteredRangeTopRow() As Long
    Dim HeaderRow As Long, LastFilterRow As Long
    On Error GoTo NoFilterOnSheet
    With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
    If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
    End With
    NoFilterOnSheet:
    End Function

    Function GetFilteredRangeBottomRow() As Long
    Dim HeaderRow As Long, LastFilterRow As Long, Addresses() As String
    On Error GoTo NoFilterOnSheet
    With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    Addresses = Split(.Range((HeaderRow + 1) & ":" & LastFilterRow).SpecialCells(xlCellTypeVisible).Address, "$")
    GetFilteredRangeBottomRow = Addresses(UBound(Addresses))
    End With
    NoFilterOnSheet:
    End Function

  • Jim Snyder

    Nicely done Rick. Not one I have seen elsewhere, but the concept was one I hadn't thought of and needed.

  • I have a problem can anybody suggest a solution. I have entered " X" in cell A10. Then I have entered 3
    in Cell "A11" with font choice Superscript. I joined Cell A10 and A11. i.e. Range("A12")=A10&A11. The result I got was X3 and not X Superscript 3. How to achieve this?

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>