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

 

[JoeMo]

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!

The active sheet is an entire worksheet. Did you mean apply the code to every sheet in the entire workbook? If so, can you explain in words what exactly you want the code to accomplish? Where (on what sheet) is the list of values you want to search for in col A?

 

[coding101]

The active sheet is an entire worksheet. Did you mean apply the code to every sheet in the entire workbook? If so, can you explain in words what exactly you want the code to accomplish? Where (on what sheet) is the list of values you want to search for in col A?

Hi Joe,

Yes, sorry for vague lingo, I want the code to detect all IDs in Column A in Sheet 4, and go through every sheet of the entire workbook and delete entire rows where it finds these IDs.

Does it make sense?

Really appreciate the help!

 

[Fluff]

As a test does this delete the unwanted rows on the active sheet

Sub coding101()
   Dim Ary As Variant

   Ary = Application.Transpose(Sheets("Sheet1").Range("A2:A200"))
   With ActiveSheet
      .Range("A1").AutoFilter 1, Ary, xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[JoeMo]

Hi Joe,

Yes, sorry for vague lingo, I want the code to detect all IDs in Column A in Sheet 4, and go through every sheet of the entire workbook and delete entire rows where it finds these IDs.

Does it make sense?

Really appreciate the help!

Vagueness causes extra effort and time wasted in multiple exchanges to get the precision required to produce working solutions. So, just to confirm:
1. You have a workbook with multiple worksheets.
2. In Sheet4 (is that the correct name or is it Sheet 4), in col A (starting in A?) is a list of IDs?
3. You want to search col A of every worksheet (except Sheet4??) for those IDs and delete the entire row for each col A cell in which any ID appears?

Is that what you want? Please answer the questions I asked in 1.- 3.

 

[coding101]

Hi Joe,

1. Yes
2. The correct name is Sheet4, and IDs are all in Column A across all sheets.
3. I want to search Column A of every worksheet, except Sheet 4, for these IDs that are found in Column A in Sheet 4, and delete the entire row for each Column A cell in which IDs from Sheet 4 appear.

Hope this is more clear.

Thank you for your help!

 

[coding101]

HI there, for some reason the code does not result in any changes. :/

 

[Fluff]

You say that the sheet is called Sheet4, but your code (which you said worked) is looking at Sheet1 for the list of ID's.
Which is correct?

 

[coding101]

I corrected the code to look for IDs in Sheet4.

Sub coding101()
Dim Ary As Variant

Ary = Application.Transpose(Sheets("Sheet4").Range("A2:A200"))
With ActiveSheet
.Range("A1").AutoFilter 1, Ary, xlFilterValues
.AutoFilter.Range.Offset(1).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

 

[coding101]

However, the code still seems not to execute any changes on my workbook