Another method to extract the last row containing data in a column

Question

I currently use this code to determine the last row containing data in column A of Sheet1

lrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

The problem is that, when I use this in a table that was extracted from a database with Excel, it will always take the very last row on the table as "lrow" even if there is no data in that row.

I remember having this issue a long time ago, and I found out that there was another way to write the code that finds lrow that actually works on tables that come from a database and are linked to a data connection. But today I've been looking for it and can't find it.

Could somebody help me with this? Thanks

Do you run that code line **after refreshing the data connection and you can see the result**? If not, you should edit your question and show all code you use. Then, when you speak about **a table**, do you mean a `listObject`, or a range on a sheet? — FaneDuru, Mar 27 '23 at 11:16
What exactly happens when you 'double the effort': `lrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).End(xlUp).Row`? — VBasic2008, Mar 27 '23 at 11:39
*even if there is no data in that row* are you 100% sure there is no data there? Also, if it's a listobject, even if the row is empty this code will return the last row of the list object itself... — Foxfire And Burns And Burns, Mar 27 '23 at 11:44
https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba — FunThomas, Mar 27 '23 at 12:59
@FaneDuru My code updates the data connection, then copies and pastes the data into another sheet to merge it with old data. Then it finds the last row containing data on this second table where I just copied my data into. I basically just want to find on column 1 what is the last row containing data on that sheet. — Sam, Mar 27 '23 at 13:36
@FoxfireAndBurnsAndBurns I'm 100% sure that there is no data. I even tried to select all of the cells that don't have data and manually delete any contents they could potentially have on them but again I had the same failure. I am sure that there is no data but lrow doesn't seem to work. — Sam, Mar 27 '23 at 13:37
You did not answer my question related to table meaning. The last row in a table (`listObject`) is obtained in a specific way. In some circumstances, it may be the same with the sheet one, on the specific column. You also did not show your code. If you use table `.QueryTable` is it something to be done, if is a query in a sheet is a different issue. Anyhow, your question has been closed and it does not accept any answer... — FaneDuru, Mar 27 '23 at 13:48

VBasic2008 · Answer 1 · 2023-03-27T12:59:58.813

The Last Row

To get the last row in a column i.e. the row of the last non-empty cell, you want to make sure that the worksheet is not filtered. Then you can freely use the Find method in the way illustrated in the following codes.
If you want to find the last non-blank row e.g. you want to exclude cells containing formulas evaluating to "" at the bottom of your data, you will use xlValues instead of xlFormulas.
xlFormulas will work even if rows are hidden (not filtered) while xlValues will not.

A Quick Fix (Not Recommended)

lrow = Worksheets("Sheet1").Columns("A").Find("*", , xlFormulas, , , xlPrevious).Row

Last Row

Sub CalculateLastRow()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    If ws.FilterMode Then ws.ShowAllData
    
    Dim lRow As Long
    
    With ws.Range("A2")
        Dim lCell As Range: Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If Not lCell Is Nothing Then lRow = lCell.Row
    End With
    
    If lRow = 0 Then
        Debug.Print "No data found."
    Else
        Debug.Print "The last row is row " & lRow & "."
    End If

End Sub

Range (more useful)

Sub ReferenceRange()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    If ws.FilterMode Then ws.ShowAllData
       
    Dim rg As Range
    
    With ws.Range("A2")
        Dim lCell As Range: Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If Not lCell Is Nothing Then Set rg = .Resize(lCell.Row - .Row + 1)
    End With

    If rg Is Nothing Then
        Debug.Print "No data found."
    Else
        Debug.Print rg.Address(0, 0)
    End If

End Sub

Q&A

Q: What does the following mean?

With ws.Range("A2")
    Set ... = .Resize(ws.Rows.Count - .Row + 1)   
End With

A: It's a way to reference the range A2:A1048576 (A2:A65536 in older versions), the range to be searched in.

Q: What does the following mean?

.Find("*", , xlFormulas, , , xlPrevious)

A: It means that there are only 3 arguments necessary for this operation: 1st - What, 3th - LookIn, and 6th - SearchDirection. The default parameter of the 2nd argument, After, is the first cell of the range (in this case A2), the 4th argument, LookAt, is irrelevant since we're using a wild card, while the 5th argument, SearchOrder is irrelevant when performing this operation in a single column or single row. The last 3 arguments (from a total of 9) aren't worth mentioning.

score -1 · Answer 2 · answered Mar 27 '23 at 11:11

It could be that the "used range" is incorrect. The used range includes any cell that has ever been used, i.e had data but then the data was deleted.

Try running something like this before your code to recalculate the used ranged.

Worksheets("Sheet1").UsedRange.Calculate

Sometimes that doesn't always work. In that case you might want to try

With Worksheets("Sheet1")
    Debug.Print .UsedRange.Address
    .UsedRange.Clear
    Debug.Print .UsedRange.Address
End With

Another method to extract the last row containing data in a column

2 Answers2

The Last Row