Delete Rows if Column A contains value from list across all worksheets

[coding101]

Hi guys, I found this code in this thread:
https://www.mrexcel.com/forum/excel...elete-rows-if-column-contains-value-list.html

And it works perfectly, but I am not sure how to apply the same code for the entire worksheet vs. just the active sheet? Any help will be super appreciated! Thank you!

Here is the code:

Sub Example1()
 
    Dim rngFound As Range, rngToDelete As Range
    Dim strFirstAddress As String
    Dim varList As Variant
    Dim lngCounter As Long
 
    Application.ScreenUpdating = False
   
    varList = Range("Sheet1!A2:A200").Value
   
    For lngCounter = LBound(varList) To UBound(varList)
   
        With ActiveSheet.Range("A:A")
            Set rngFound = .Find( _
                                What:=varList(lngCounter, 1), _
                                Lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True _
                                    )
           
            If Not rngFound Is Nothing Then
                If rngToDelete Is Nothing Then
                    Set rngToDelete = rngFound
                Else
                    Set rngToDelete = Application.Union(rngToDelete, rngFound)
                End If
               
                strFirstAddress = rngFound.Address
                Set rngFound = .FindNext(After:=rngFound)
               
                Do Until rngFound.Address = strFirstAddress
                    Set rngToDelete = Application.Union(rngToDelete, rngFound)
                    Set rngFound = .FindNext(After:=rngFound)
                Loop
            End If
        End With
    Next lngCounter
   
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
 
    Application.ScreenUpdating = True
 
End Sub

 

[Fluff]

On the sheets you want to delete rows, do you have a header row in row 1?

 

[JoeMo]

Here's some alternative code you can try.

Sub coding101()
Dim Ws As Worksheet, IDs As Variant, SourceWs As Worksheet
Set SourceWs = Sheets("Sheet4")
IDs = Intersect(SourceWs.Range("A:A"), SourceWs.UsedRange).Value
Application.ScreenUpdating = False
For Each Ws In Worksheets
    If Ws.Name <> SourceWs.Name Then
        For i = 1 To UBound(IDs, 1)
            If IDs(i, 1) <> "" Then
                Ws.Range("A:A").Replace what:=IDs(i, 1), replacement:="#N/A", lookat:=xlWhole
            End If
        Next i
    End If
    On Error Resume Next
    Ws.Range("A:A").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
Next Ws
Application.ScreenUpdating = True
End Sub

 

[coding101]

It works! Thank you so much! The only problem is that it deletes the first row in all sheets except for Sheet4. Is there is a way to fix it? Thank you for all your help!

 

[coding101]

I do. The first row is a header.

On the sheets you want to delete rows, do you have a header row in row 1?

 

[JoeMo]

It works! Thank you so much! The only problem is that it deletes the first row in all sheets except for Sheet4. Is there is a way to fix it? Thank you for all your help!

You cling to vagueness. What works - you have been given different solutions by at least 2 respondents, can you give us an idea to whom you are addressing this reply????

 

[coding101]

Apologies. The code that you have shared deletes the first row of every sheet except for Sheet 4. Can you help solving it? Thank you!

You cling to vagueness. What works - you have been given different solutions by at least 2 respondents, can you give us an idea to whom you are addressing this reply????

 

[JoeMo]

Apologies. The code that you have shared deletes the first row of every sheet except for Sheet 4. Can you help solving it? Thank you!

Probably b/c you didn't specify in what cell in sheet4 the actual IDs begin and you are using the same header as in all the other sheets.

See if this fixes the problem:

Sub coding101()
Dim Ws As Worksheet, IDs As Variant, SourceWs As Worksheet
Set SourceWs = Sheets("Sheet4")
IDs = Intersect(SourceWs.Range("A:A"), SourceWs.UsedRange.Offset(1, 0)).Value
Application.ScreenUpdating = False
For Each Ws In Worksheets
    If Ws.Name <> SourceWs.Name Then
        For i = 1 To UBound(IDs, 1)
            If IDs(i, 1) <> "" Then
                Ws.Range("A:A").Replace what:=IDs(i, 1), replacement:="#N/A", lookat:=xlWhole
            End If
        Next i
    End If
    On Error Resume Next
    Ws.Range("A:A").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
Next Ws
Application.ScreenUpdating = True
End Sub

 

[coding101]

I am using the same headers in all sheets including Sheet4. The code did not fix the problem. It now replaces IDs that it finds on Sheet4 as NAs through the rest of the sheets.

Probably b/c you didn't specify in what cell in sheet4 the actual IDs begin and you are using the same header as in all the other sheets.

See if this fixes the problem:

Sub coding101()
Dim Ws As Worksheet, IDs As Variant, SourceWs As Worksheet
Set SourceWs = Sheets("Sheet4")
IDs = Intersect(SourceWs.Range("A:A"), SourceWs.UsedRange.Offset(1, 0)).Value
Application.ScreenUpdating = False
For Each Ws In Worksheets
    If Ws.Name <> SourceWs.Name Then
        For i = 1 To UBound(IDs, 1)
            If IDs(i, 1) <> "" Then
                Ws.Range("A:A").Replace what:=IDs(i, 1), replacement:="#N/A", lookat:=xlWhole
            End If
        Next i
    End If
    On Error Resume Next
    Ws.Range("A:A").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
Next Ws
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

I am using the same headers in all sheets including Sheet4. The code did not fix the problem. It now replaces IDs that it finds on Sheet4 as NAs through the rest of the sheets.

That's b/c I edited post#17 while you were copying the code. Go back to post#17 and copy/paste to VBE the code there again. It should be fine now. In the code you used I had replaced the deletion of all rows containing NAs with the selection of those rows to run some diagnostics.