VBA Delete Table Rows based upon Cell Value

[Martyn Pattison]

Got great feedback yesterday to aid my current build, however having succeeded with VBA to add line I am far from successful with my various approaches to delete "Completed" lines. My idea was to sort by Column Z and then delete the Completed lines which would be all at the bottom. Failed so far. Appreciate any pointers.

 

[Martyn Pattison]

Managed to get somewhere! The vba below works but does not delete all Completed lines in one run?

>>> use code - tags <<<

Sub ClearDeleteRows()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim i As Long
   
    ' Set the worksheet and table references
    Set ws = ActiveSheet
    Set tbl = ws.ListObjects("Table1")
   
    ' Loop
    For i = tbl.ListRows.Count To 1 Step -1
   
        ' Check for z
        If tbl.DataBodyRange(i, "z").Value = "z" Then
            ' Clear row
            tbl.DataBodyRange(i).ClearContents
        End If
       
        ' if the row is blank
        If WorksheetFunction.CountA(tbl.DataBodyRange(i)) = 0 Then
            ' Delete the row if it's blank
            tbl.ListRows(i).Delete
        End If
    Next i
End Sub

 

[p45cal]

There's potentially quite a lot which can go wrong with that code.
So 2 questions:
1. You seem to be trying to look at column Z; is this column Z of the entire sheet (column 26 of the sheet)?, or column Z of the table's range (column 26) of the table itself? These could be the same column if the table starts in column A of the sheet, otherwise they'll be different.
2. What is the header of that column you want to look at? This will be a more robust way of referring to that column.

The following (a guess) is the sort of thing I think you're looking for. It deletes rows which are entirely blank or have a 'z' (as the cell's entire contents) in the column headed Hdr6 (obviously you'll need to change Hdr6 in the code to the header of the column containing zs.). No sorting is necessary.

Sub ClearDeleteRows()
Dim ws As Worksheet
Dim tbl As ListObject, i As Long, rngCompletedColm As Range
 
' Set the worksheet and table references
Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table1")
Set rngCompletedColm = tbl.ListColumns("Hdr6").DataBodyRange 'the column in the table you want to look for "z" in
' Loop
For i = tbl.ListRows.Count To 1 Step -1
  If WorksheetFunction.CountA(tbl.ListRows(i).Range) = 0 Or Intersect(rngCompletedColm, tbl.ListRows(i).Range) = "z" Then tbl.ListRows(i).Delete
Next i
End Sub

or:

Sub ClearDeleteRows()
Dim i As Long, rngCompletedColm As Range
 
With ActiveSheet.ListObjects("Table1")
  Set rngCompletedColm = .ListColumns("Hdr6").DataBodyRange 'the column in the table you want to look for "z" in
  ' Loop
  For i = .ListRows.Count To 1 Step -1
    With .ListRows(i)
      If WorksheetFunction.CountA(.Range) = 0 Or Intersect(rngCompletedColm, .Range) = "z" Then .Delete
    End With
  Next i
End With
End Sub