Deleting worksheet row based on value tied to a table

Question

I have a worksheet with two tables on it located starting in column B. In Column A I have a COUNTA formula that is tied to a delete blank rows button. My code works great to delete the table row but i need it to delete the entire worksheet row so that it also deletes the formula in column A instead of it continuously shifting down as rows are added or deleted.

The trouble is that I have two tables on the sheet so I need the deletion row action to refer only to the Local_1 table and the loop to stop when it reaches the end of that table.

Any suggestions on how to delete the entire row and not just the table row?

Dim i As Long
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr
Rows.EntireRow.Hidden = False
With ActiveSheet.ListObjects("Local_1")
For i = .ListRows.Count To 1 Step -1
        If .ListRows(i).Range.Cells(0) <= 0 Then
            .ListRows(i).Delete                
        End If
    Next i
End With

`Cells()` is 1-based, so `If .ListRows(i).Range.Cells(1) = 0 Then` — Tim Williams, Nov 01 '18 at 23:37

score 1 · Answer 1 · answered Nov 02 '18 at 02:43

List Entire Row

To not cramp your style, just replace

If .ListRows(i).Range.Cells(0) <= 0 Then
    .ListRows(i).Delete                
End If

with

If .ListRows(i).Range.Cells(1) <= 0 Then 'as Tim Williams mentioned
    '.ListRows(i).Delete
    ActiveSheet.Rows(Range(.DataBodyRange.Address).Row + i - 1).Delete
End If

But (to cramp your style) I would strongly suggest you use (declare) variables like this:

Sub ListEntireRow()

  Dim i As Long
  Dim oWs As Worksheet
  Dim oList As ListObject

  Application.ScreenUpdating = False

  Set oWs = ActiveSheet
  Set oList = oWs.ListObjects("Local_1")

  oWs.Unprotect Password:=pswStr
  Rows.EntireRow.Hidden = False

  With oList
    For i = .ListRows.Count To 1 Step -1
      If .ListRows(i).Range.Cells(1) <= 0 Then
'        .ListRows(i).Delete
        oWs.Rows(Range(.DataBodyRange.Address).Row + i - 1).Delete
      End If
    Next i
  End With

  Application.ScreenUpdating = True

End Sub

Now you have the Intellisense for the Worksheet (oWs) and the ListObject (oList) working for you i.e. you can see their properties and methods.

Once I changed `If .ListRows(i).Range.Cells(1) <= 0` Then to `Cells(0)` to refer to my formula outside of the table in column A this worked perfectly. Thanks. — JessicaGMG, Nov 02 '18 at 15:54

Deleting worksheet row based on value tied to a table

1 Answers1

List Entire Row