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,

 

[Z51]

Does this work?

Sub zero269()
Dim lastrow As Long, i As Long
lastrow = Range("Table1").Rows.Count + 1

For i = lastrow To 2 Step -1
    If Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete xlShiftUp
Next i
End Sub

It assumes your table headers are in row 1 (which is why I add 1 to the Rows.Count value, and that the table is named Table1. Adjust as needed.

 

[Peter_SSs]

I'm trying to delete empty rows in a Table

Given the above and your use of .EntireRow in your code, I am unsure whether you are trying to delete entire rows in the worksheet of only table rows.

In any case you should be able to use code line A or B as appropriate. It doesn't matter what row or what column your table starts in (so long as the table name is correct).

Sub Del_Rws()
  Dim r As Long

  With ActiveSheet.ListObjects("Table1").ListRows
    For r = .Count To 1 Step -1
      If Len(.Item(r).Range.Cells(1).Value) = 0 Then .Item(r).Delete                  'A
'      If Len(.Item(r).Range.Cells(1).Value) = 0 Then .Item(r).Range.EntireRow.Delete  'B
    Next r
  End With
End Sub

 

[DanteAmor]

ActiveSheets.ListObjects (1) returns Compile error: Variable not defined.

You have an "s" left over on that line

----- --

This is another way to read the rows only from the table.
If you only have one table in the sheet you can use ListObjects(1), otherwise you must put the table name in quotes

Sub deleterows()
  Dim tbl As ListObject
  Dim i As Long
  
  Set tbl = ActiveSheet.ListObjects(1)
  For i = tbl.DataBodyRange.Rows.Count To 1 Step -1
    If tbl.DataBodyRange.Cells(i, 1).Value = "" Then
      tbl.ListRows(i).Delete
    End If
  Next
End Sub

 

[zero269]

Does this work?

Sub zero269()
Dim lastrow As Long, i As Long
lastrow = Range("Table1").Rows.Count + 1

For i = lastrow To 2 Step -1
    If Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete xlShiftUp
Next i
End Sub

It assumes your table headers are in row 1 (which is why I add 1 to the Rows.Count value, and that the table is named Table1. Adjust as needed.

Hi Z51,

I tested your code, and it definitely works. My sample Table header does start in Row 1, and I changed "Table1" to my table name before testing.

One thing to note is that it deletes the entire Sheet Row vs. the Table Rows. No biggie considering my sample code was using the EntireRow.Delete as well. Using my sample data, it correctly recognized the two rows in the Table that needed to be deleted but also deleted the Sheet Data in Column E.

I was comparing yours to Peter's code, but I couldn't figure out how to modify yours to make the Table selection dynamic and convert it from deleting the EntireRow vs. just the Table Rows.

Sub testDeleteRowIfCellBlank()
' Z51 VBA Code 1229323/post-6015821

    Dim lastrow As Long, i As Long
    lastrow = Range("tblBooks").Rows.Count + 1
    
    For i = lastrow To 2 Step -1
        If Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete xlShiftUp
    Next i

End Sub

Thank you for taking the time to share this. It's very much appreciated.

 

[zero269]

Given the above and your use of .EntireRow in your code, I am unsure whether you are trying to delete entire rows in the worksheet of only table rows.

In any case you should be able to use code line A or B as appropriate. It doesn't matter what row or what column your table starts in (so long as the table name is correct).

Sub Del_Rws()
  Dim r As Long

  With ActiveSheet.ListObjects("Table1").ListRows
    For r = .Count To 1 Step -1
      If Len(.Item(r).Range.Cells(1).Value) = 0 Then .Item(r).Delete                  'A
'      If Len(.Item(r).Range.Cells(1).Value) = 0 Then .Item(r).Range.EntireRow.Delete  'B
    Next r
  End With
End Sub

Hi Peter,

It's good to see you again... so to speak.

Thanks for the VBA code and clarification on how it works. After looking at yours and Z51's.... comparing to what I had, I can see where I was way off.
This is very insightful and very much appreciated.

I was able to make one small change from some earlier - misused code - to make the Table selection more dynamic which I was pleased to see it still worked.
From ActiveSheet.ListObjects("Table1") to ActiveSheet.ListObjects(1)

I went with your option A because I currently only want to delete the Table Rows.

Out of curiosity, is there a way to select the Table that is active based on the ActiveCell without having to specify the Table Name or the Table Index?

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

    Dim LastRow As Long
    With ActiveSheet.ListObjects(1).ListRows ' or ("tblBooks")
        For LastRow = .Count To 1 Step -1
            If Len(.Item(LastRow).Range.Cells(1).Value) = 0 Then .Item(LastRow).Delete    ' Delete Table Rows
            'If Len(.Item(LastRow).Range.Cells(1).Value) = 0 Then .Item(LastRow).Range.EntireRow.Delete  ' Delete Sheet Rows
        Next LastRow
    End With
    
End Sub

 

[zero269]

You have an "s" left over on that line

----- --

This is another way to read the rows only from the table.
If you only have one table in the sheet you can use ListObjects(1), otherwise you must put the table name in quotes

Sub deleterows()
  Dim tbl As ListObject
  Dim i As Long
 
  Set tbl = ActiveSheet.ListObjects(1)
  For i = tbl.DataBodyRange.Rows.Count To 1 Step -1
    If tbl.DataBodyRange.Cells(i, 1).Value = "" Then
      tbl.ListRows(i).Delete
    End If
  Next
End Sub

Hi Dante,

Many thanks to you, too. Your code does the trick as well.
Although I'm not very good at "building code", interpreting it always amazes me when compared to other similar code that achieves the same results.

Thank you and best regards,

 

[Peter_SSs]

Out of curiosity, is there a way to select the Table that is active based on the ActiveCell without having to specify the Table Name or the Table Index?

See if this would help

Sub Test()
  Dim tblName As String
  
  On Error Resume Next
  tblName = ActiveCell.ListObject.Name
  On Error GoTo 0
  If tblName = "" Then
    MsgBox "Active cell is not in a table"
  Else
    MsgBox "Active cell is in table: " & tblName
  End If
End Sub

 

[DanteAmor]

Out of curiosity, is there a way to select the Table that is active based on the ActiveCell without having to specify the Table Name or the Table Index?

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

 

[zero269]

See if this would help

Sub Test()
  Dim tblName As String
 
  On Error Resume Next
  tblName = ActiveCell.ListObject.Name
  On Error GoTo 0
  If tblName = "" Then
    MsgBox "Active cell is not in a table"
  Else
    MsgBox "Active cell is in table: " & tblName
  End If
End Sub

Thanks Pete... I just tested it and it definitely identified the Table by the ActiveCell. I was able to see the name in the Locals window, and then your MsgBox served as a secondary confirmation.