MS Excel - Macro to Delete particular values in Separate Tabs

[santa12345]

Hello.
I am trying to write a macro to do the following.
I have a list of values that I would like to find on each tab and remove the entire row of data.
Please reference the attachment. Hopefully that will help.
Unfortunately my real example ...the tabs will be randomly named with over 100+ tabs.
Any guidance would be greatly appreciated.
Thanks !!!!

 

[rpaulson]

try this on a copy of you file.

Sub delete_car()
Application.ScreenUpdating = False
For Each ws In Worksheets

If ws.Name = ActiveSheet.Name Then GoTo 99

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row

car = Cells(r, "A")

On Error Resume Next
    With ws.Range("A:A")
        .Replace car, False, xlWhole ' can also use *car* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
On Error GoTo 0

Net r

99 Next ws
Application.ScreenUpdating = True
End Sub

hth,
Ross

 

[EXCEL MAX]

Hello Santa,
here is one more...
Change sheet name in the code or rename your sheet.

Sub RemoveErrorRows()
  
    Dim varWS As Worksheet, varWS2 As Worksheet
    Dim varNRows As Long, varNRows2 As Long
    Dim varRange1 As Range, varRange2 As Range, _
        varRange3 As Range, varRange4 As Range
    Dim varNErrors As Long
  
    Application.ScreenUpdating = False
    Set varWS = Worksheets("YourSheetName")
    varNRows = varWS.UsedRange.Rows.Count
    Set varRange2 = varWS.Range("A2:A" & varNRows)
    For Each varWS2 In Worksheets
        If Not varWS2.Name = "YourSheetName" Then
            For Each varRange1 In varRange2
                varNRows2 = varWS2.UsedRange.Rows.Count
                Set varRange4 = Sheets(varWS2.Index).Range("A1:A" & varNRows2)
                varRange4.AutoFilter 1, varRange1.Value
                varNErrors = WorksheetFunction.CountIf _
                    (Sheets(varWS2.Index).Range("A2:A" & varNRows2), varRange1.Value)
                If varNErrors > 0 Then
                    Application.DisplayAlerts = False
                    Sheets(varWS2.Index).Range("A2:A" & varNRows2). _
                    SpecialCells(xlCellTypeVisible).Delete
                End If
            Next
            varWS2.ShowAllData
            varRange4.AutoFilter
        End If
    Next
    Application.ScreenUpdating = True
  
End Sub