Delete Table Row Based on Criteria VBA

Question

I am attempting to delete a specific table row based on values in two columns. I attempted to apply filters to the table columns to narrow my criteria, but once I click delete, the ENTIRE ROW is deleted causing values outside of the table to be deleted. Also, the macro recorder isn't as dynamic as I'd like it to be, since it ONLY selects the cell I clicked while recording.

   Sub Macro2()
    '
    ' Macro2 Macro
    '
    '
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
    "Apple"                               \\Narrowing criteria in Column 1 of the table
         Range("A4").Select               \\This only applies to a specific cell, and the value can shift
         Selection.EntireRow.Delete       \\This will delete the entire sheet row, I'd like for only the table row to be deleted    
         Range("A5").Select
         Selection.EntireRow.Delete
         Selection.EntireRow.Delete
    End Sub

Is there a way to find the desired string in a column and delete only the rows in the table once the criteria is met? I attempted to only delete the ListObject.ListRows, but it only references the row I've selected, and not the one based off criteria.

Try `DataBodyRange.Delete` instead of `Selection.EntireRow.Delete`. This post may help you out. https://stackoverflow.com/questions/20663491/delete-all-data-rows-from-an-excel-table-apart-from-the-first — Zack E, Feb 12 '19 at 22:14

Tate Garringer · Answer 1 · 2019-02-14T04:32:34.473

4

You could use .DataBodyRange and .SpecialCells(xlCellTypeVisible) to set a range variable equal to the filtered ranges, then unfilter and delete:

Dim dRng As Range
With ActiveSheet.ListObjects("Table1")
    .Range.AutoFilter Field:=1, Criteria1:="Apple"
    If WorksheetFunction.Subtotal(2, .DataBodyRange) > 0 Then
        Set dRng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        .Range.AutoFilter
        dRng.Delete xlUp
    End If
End With

edited Feb 14 '19 at 04:32

answered Feb 12 '19 at 22:22

Tate Garringer

1,509
1
6
9

Almost there! One issue is that if the criteria isn't there, in this example "Apple", I get a Run-Time error '1004': Application-defined or object-defined error . This table changes month to month, so the value isn't always there. – MSauce Feb 13 '19 at 15:19
Use a Subtotal Count to test for how many cells exist in your filtered table. I'll edit my answer to reflect that. – Tate Garringer Feb 14 '19 at 04:31

Mr Watt · Answer 2 · 2019-02-12T22:48:58.687

You will have to indicate which cells/range you want to delete. You can find the relevant row by using the find function. Since your table is static I would propose the following macro. A for loop checking each row is also possible, but not so efficient for a very large table. It can be useful to prepare your dataset by adding a flag to column c (e.g. a 1 if to be deleted).

EDIT suggestion by Tate also looks pretty clean

Sub tabledelete()

Dim ws As Worksheet
Dim rangecheck As Range
Dim rcheck As Integer

Set ws = Sheets("Sheet1") 'fill in name of relevant sheet
Set rangecheck = Range("A1") ' dummy to get the do function started

Do While Not rangecheck Is Nothing
With ws
    With .Range("C2:C30") ' fill in relevant range of table
        Set rangecheck = .Find(what:=1, LookAt:=xlWhole)
    End With
If Not rangecheck Is Nothing Then 'only do something if a 1 is found
rcheck = rangecheck.Row
.Range(.Cells(rcheck, 1), .Cells(rcheck, 3)).Delete Shift:=xlUp 'delete 3 columns in row found
End If

End With

Loop

End Sub

Delete Table Row Based on Criteria VBA

2 Answers2

Linked