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

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

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.

### A Better Solution?

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

____________

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

Works every time

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)

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.

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.

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

Hi Debra,

how about resizing the table like this:

ws.ListObjects(1).Resize ws.ListObjects(1).DataBodyRange.CurrentRegion

Thanks for all the suggestions!

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

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

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