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?
Video: Create an Excel Named Table
When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps
____________
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
Just wanted to say a huge thanks for this – couldn’t seem to find a working solution anywhere, and spent way too much time trying to resize a table. This works perfectly – I love the work around.
Thank you!