Excel Vba Macro to Delete Row or Column Based on Cell Value and Column Name

Question

Can the below be altered to do the following: Delete a row based on a column name and cell value? or multiple column names and cells? the example being: i want to delete anything that has a value of 'Complete' in column named 'Status' but I may also want to delete anything 'Complete' in Column named 'Second Status' as well but not delete the row if "completed" is in column "ColumnA" and across multiple sheets as well...I know this is asking for a lot..

Hope that makes sense, and ty in advance!

Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim sFirstAddress As String
Dim sh As Worksheet

strSearch = "Completed"
Set rDelete = Nothing

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
With sh.Columns("A:AO")
Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
If Not rFind Is Nothing Then
    sFirstAddress = rFind.Address
    Do
        If rDelete Is Nothing Then
            Set rDelete = rFind
        Else
            Set rDelete = Application.Union(rDelete, rFind)
        End If
        Set rFind = .FindNext(rFind)
    Loop While Not rFind Is Nothing And rFind.Address <> sFirstAddress

    rDelete.EntireRow.Delete
    Set rDelete = Nothing
End If
End With
Next sh
Application.ScreenUpdating = False
End Sub

Unknown Unknown · Accepted Answer · 2017-03-14T22:38:10

The .AutoFilter method provides quick subsets of data for copying or deletion.

Option Explicit

Sub Macro1()
    Dim vCOLs As Variant

    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        vCOLs = Array(Application.Match("Status", .Rows(1), 0), _
                      Application.Match("Second Status", .Rows(1), 0), _
                      1)
        With .Cells(1, 1).CurrentRegion
            'do not delete rows with 'Completed' in first column
            .AutoFilter field:=vCOLs(2), Criteria1:="<>Completed"
            'delete rows with 'Complete' in Status column
            .AutoFilter field:=vCOLs(0), Criteria1:="Complete"
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Cells.EntireRow.Delete
                End If
            End With
            .AutoFilter field:=vCOLs(0)
            'delete rows with 'Complete' in Second Status column
            .AutoFilter field:=vCOLs(1), Criteria1:="Complete"
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Cells.EntireRow.Delete
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Excel Vba Macro to Delete Row or Column Based on Cell Value and Column Name

1 Answers