Excel VBA: find the last empty cell in an Excel table, not range

Question

I found that the following code doesn't return the last used row number in that particular column.

ThisWorkbook.Sheets(1).Cells(max_row, last_data_column + 1).End(xlUp).Row

What it returns is the last used row number in the Excel table.

I have an Excel table with the range A1:AB142. In the last row (142), only column B has data, the rest being empty. The above code returns 142, not 141, no matter what the last_data_column is (I tried 22, and 23).

Similarly,End(xlDown) doesn't work properly. Even though only W1 has data and the rest of the first row is blank,

ThisWorkbook.Sheets(1).Range("W1").End(xlDown).Row

gives 2, when W142 is blank and W1 to W141 are not blank.

How to find the last empty cell in a particular column in an Excel table?,

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2019-08-18T11:19:44

Use the .Find method to find the last row with data in your table.

Then offset by one to fine the last empty row.

Something like (to get the last row):

    Dim LO As ListObject
    Dim C As Range

Set LO = Sheet1.ListObjects("Table1")

With LO.Range.Columns(column_to_check) 'column_to_check is relative to the LO.Range
    Set C = .Find(what:="*", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlPrevious)
    If Not C Is Nothing Then
        'do stuff`
        Debug.Print C.Row+1 'last empty row
          'If the row is the last in the table, then column is full
    End If

To find the First empty row, something like:

With LO.Range.Columns(1)
    Set C = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext)
    If Not C Is Nothing Then
        'do stuff`
        Debug.Print C.Row 'First empty row
    End If
End With

Excel VBA: find the last empty cell in an Excel table, not range

1 Answers