Finding last row containing data not working - possible problem with table formatting [duplicate]

Question

I've got a sheet called MachineData and I've got the following code

Worksheets("MachineData").Activate

'Last row with data on column AD (30)
lrow3 = Cells(Rows.Count, 30).End(xlUp).Row

The problem, is that lrow3 returns 2166 when it should return 1 because that's the last row on that column that contains data.

This column was added next to a table containing data that I extracted from a database. I believe that because the data was extracted from the database then this table has some weird formatting that makes this function not work.

My question is, how can I change the formatting of my table so this doesn't happen? or is there a way to change my code so this doesn't happen within this table?

Here is a picture of what my column looks like, anything below "File Name" is empty. I just get those green and white cell colours as this is part of the larger table.

Answer 1

For your particular context, i.e. with blank cells at the end of your table

lrow3 = Cells(Rows.Count, 30).End(xlUp).End(xlUp).Row 

Answer 2

Last Non-Blank Row Using the Find Method

• It is assumed that the worksheet is neither filtered nor has hidden rows.
• Blank means empty, ="", ', ...
• Empty means empty.
• Note that the only difference between the two procedures is the parameter of the 3rd Find method argument, called LookIn, set to xlValues (blank) or xlFormulas (empty).

Non-Blank

Sub LastNonBlankRow() ' is different than last non-empty row
    
    Const FirstCellAddress As String = "AD2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook 'workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("MachineData")
    Dim lRow As Long
    
    With ws.Range(FirstCellAddress)
        Dim lCell As Range
        Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlValues, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column
        lRow = lCell.Row
    End With
    
End Sub

Non-Empty

Sub LastNonEmptyRow()
    
    Const FirstCellAddress As String = "AD2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook 'workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("MachineData")
    Dim lRow As Long
    
    With ws.Range(FirstCellAddress)
        Dim lCell As Range
        Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column
        lRow = lCell.Row
    End With
    
End Sub