Last Row Incorrect With Excel Table

When you’re working with Excel VBA, you might want find the last row with data, so you can paste new data in the row below that.

The following code works up from the last row on the worksheet, until it hits a cell with data. It’s like using the End key and Up arrow, to manually move from the bottom of the worksheet.

Sub GetLastRow()
Dim ws As Worksheet
Dim lRow As Long

Set ws = ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lRow

End Sub

In the sample sheet, the last data  in column A is in row 10, and that’s the result when running the code.

lastrowtable01

Unexpected Result

This week, I was using similar code in a client’s workbook, to find the last row of data. The data was in a named Excel table, and the last couple of rows in the table were blank. I wanted to find the last row with data, so I could change the table definition, to end at that last row.

When I used the same Excel VBA code, the last row wasn’t what I expected – it found the last row in the named table, even though that row was empty.

lastrowtable02

I’m sure that information is helpful in some situations, but it sure wasn’t going to help me resize the table!

My Last Row Workaround

There might be a more sophisticated solution to this problem, but I added a line of code to resize the table, so it ends at row 2. Then, the code found the correct last row of data, and resize the table to end at that row.

Sub ResizeTheTable()
Dim ws As Worksheet
Dim lRow As Long

Set ws = ActiveSheet
ws.ListObjects(1).Resize ws.Range("$A$1:$H$2")

lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.ListObjects(1).Resize ws.Range("$A$1:$H$" & lRow)

MsgBox lRow

End Sub

That fixed the problem, and the table resized correctly.

lastrowtable03

A Better Solution?

Have you run into this problem with named Excel tables? How did you solve it?

____________

You may also like...

12 Responses

  1. sam says:

    EndRow = Cells.Find(“*”, After:=Range(Cells(Rows.Count, Columns.Count), Cells(Rows.Count, Columns.Count)), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Works every time

  2. Roger Govier says:

    Hi Deb

    Yes, been bitten by that one, especially when importing new sets of data to an exiting table.
    I ended up clearing all of the data apart from the first row below the header, resizing, copying in, finding the last row and then resizing again.
    Almost identical to you!!! All that “training” must have rubbed off!!!

    wsD.Range(“A8:AD1000000?).ClearContents
    lr2 = wsD.Cells(Rows.Count, 1).End(xlUp).Row
    wsD.ListObjects(“Mydata”).Resize wsD.Range(“A6:AD9?)

    wsF.Range(“A7:AB” & lr).Copy wsD.Range(“A7?)
    lr2 = wsD.Cells(Rows.Count, 1).End(xlUp).Row

    wsD.ListObjects(“Mydata”).Resize wsD.Range(“A6:AD” & lr2)

  3. Rick Rothstein (MVP - Excel) says:

    This code line seems to work correctly…

    LastRowWithAnyDataInIt = Cells.Find(What:=”*”, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row

    It is what I used for non-tables, but it seems to work with them as well.

  4. Rick Rothstein (MVP - Excel) says:

    It looks like that might fit all on one line (I’ll shorten the variable name to be sure)…

    LastRow = Cells.Find(What:=”*”, SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

    Note that this formula ignores cells with formulas that are displaying the empty string. If your situation is such that you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.

  5. Gregory says:

    This will work equally well.

    Sub DeleteLastEmptyRowsOfTable()
    “”””””””””””””””””””””””””””””
    ‘ Will delete the last empty rows of the first ListObject
    ‘ (Table1) by looking in column 1, starting with the last
    ‘ row and moving up the table. If the table headers are
    ‘ shown the list object range is 1 less than the table
    ‘ range as the default range for a Table is the data.
    ‘ The HrdRow variable accounts for this in deleting the
    ‘ last empty row.
    “”””””””””””””””””””””””””””””‘
    Dim rng As Range
    Dim LO As ListObject
    Dim i As Long
    Dim HdrRow As Integer

    ‘ Set the Table object variable and a range object
    Set LO = Sheet1.ListObjects(1)
    Set rng = LO.Range

    ‘ Account for the header row
    If LO.ShowHeaders Then HdrRow = 1

    ‘ Loop through the table and delete empty rows
    For i = rng.Rows.Count To 2 Step -1
    If rng(i, 1) = “” Then
    LO.ListRows(i – HdrRow).Delete
    Else
    Exit For
    End If
    Next i
    End Sub

  6. Dominik Petri says:

    Hi Debra,

    how about resizing the table like this:
    ws.ListObjects(1).Resize ws.ListObjects(1).DataBodyRange.CurrentRegion

  7. Thanks for all the suggestions!

  8. Contextures Blog » Find Last Row With Excel VBA says:

    […] 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 […]

  9. Juanita says:

    Thank you Gregory – yours was the only answer that catered for rows that had been added then deleted

  10. Kamran says:

    Private Sub myButton_Click()
    Dim lastRow As Long
    Dim drcSheet As Worksheet

    Set drcSheet = ThisWorkbook.Worksheets(“My Table”)

    With drcSheet.ListObjects(“tabActiveProjs”)
    lastRow = .Range.Cells(.ListRows.Count, 2).End(xlUp).Row
    End With

    MsgBox lastRow

    End Sub

  1. January 12, 2012

    […] Then I noticed that there was a formatted Excel table on the data collection sheet, which wasn't in my original file. That can cause problems if you're using Excel VBA to add data to the first blank row on the worksheet. […]

  2. September 24, 2012

    […] XML Documents in Database QueriesAdding Vertical Space Between Two Horizontal Lines in a TableLast Row Incorrect With Excel Table var base_url_sociable = […]

Leave a Reply

Your email address will not be published. Required fields are marked *