VBA to Delete Table Rows with Column A Blank Cell

[zero269]

Hello,

I'm trying to delete empty rows in a Table where the cell in Column A is empty/blank.

I'm using the following which only works if there is NO data below the Table I'm trying to run this on.

Sub DeleteRowIfCellBlank()
' Delete Table Rows with blank cells in Column A

Application.ScreenUpdating = False
    
    Dim LastCell As Long
    LastCell = Cells(Rows.Count, 1).End(xlUp).Row
    
    MsgBox "Last Cell is " & LastCell ' I'm only using this line to show me the Row value it thinks is the LastCell
    Range("A2:A" & LastCell).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
Application.ScreenUpdating = True

End Sub

I'm seeing the following two behaviors:

1. If there is NO data below the Table, the LastCell (variable) returns the correct row number, and the Blank Rows are deleted from the Table.
2. If there IS data below the Table, the LastCell (variable) returns the row number for that data below the Table and then returns some generic 400 error; that's all it displays.
   • For example, if the last Table Row is 10, and I have data in Row 15 (below the Table), my variable returns the value of 15.

I've tried looking into forcing the VBA code to work only within the active Table and not the Entire Sheet by attempting to activate the Sheet and/or Table first.
Each one of the following were tested individually and all resulted in errors. I recorded the errors for each one.

• ActiveSheet.Activate returns Run-time error '1004': Delete method of Range class failed.
• ActiveSheets.ListObjects (1) returns Compile error: Variable not defined.
• ActiveCell.ListObject.Name returns Compile error: Invalid use of property.

Does anyone know the proper way to activate the Table so this code can run only within the range of the Table?

Thank you,

 

[zero269]

A way to select the table according to the active cell.

Sub select_table()
  Dim tbl As ListObject
  For Each tbl In ActiveSheet.ListObjects
    If Not Intersect(tbl.Range, ActiveCell) Is Nothing Then
      tbl.Range.Select
    End If
  Next
End Sub

Thanks Dante, your method works as well.
I added

MsgBox tbl

just to pop it up on screen.