Can .End(xlUp) skip blank rows/cells at the bottom of an Excel table data range?

Question

I currently have a program that determines the next empty row by using this code:

Public Function GetEmptyRow() As Long
    If ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row > _
    ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row Then
        GetEmptyRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row + 1
    Else
        GetEmptyRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If
End Function

The greater than comparison is there because if the item in the active row is used by more than one person, it creates a new row and nests the second name in column E, with columns A through D being blank.

I was trying to add striped formatting with an Excel table, and now when a new row is added, the blank cells that are in A through D in the data range "stop" the End(xlUp) value, allowing me to change to just one line of code:

Public Function GetEmptyRow() As Long
    GetEmptyRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
End Function

If any rows are deleted at the bottom, however, it returns the row under the last in the range, even if it's blank. Is there a way to skip blanks in an Excel table data range using .End(xlUp)?

Update After reading the suggestions, I ended up changing the code to this:

Public Function GetEmptyRow() As Long

    Dim lastRow1 As Integer, lastRow2 As Integer

    lastRow1 = ActiveSheet.ListObjects("Table3").Range.Columns(2) _
    .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastRow2 = ActiveSheet.ListObjects("Table3").Range.Columns(5) _
    .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    If lastRow2 > lastRow1 Then
        GetEmptyRow = lastRow2 + 1
    Else
        GetEmptyRow = lastRow1 + 1
    End If

End Function

Which seems to do the trick. I'm not sure if this is the most efficient way but it definitely gets the job done. Thanks for the suggestions!

You could test if the cell 1 row above the result of End(xlUp), i.e. a blank row in the table, is empty, and then use End(xlUp) again to get to the last populated row in the table. Or you could use the properties and methods of the table to find the last non-empty row, which might be a better approach. — BigBen
Note that in your revised code, you should still handle the possibility of there being no empty rows. Your lines lastRow1 = ActiveSheet... and lastRow2 = ActiveSheet... will throw an error if Cells.Find doesn't find anything. — BigBen

girlvsdata girlvsdata · Accepted Answer · 2018-08-22T22:52:48

You can't skip blanks using the .End property, it mimics the action that would take place if you clicked a cell and pressed ctrl+up or end+up on your keyboard.

If you start from a non-blank cell, it will automatically stop where the range is blank, or
If you start from a blank cell, it will automatically stop where the range is not blank.

Here is the MSDN documentation on the property and here is another very good answer on the topic.

Can .End(xlUp) skip blank rows/cells at the bottom of an Excel table data range?

1 Answers