Comparing column to column in multiple sheets, delete row of match found

[Mike820]

I have account numbers in E2 of SheetA . I would like to compare account numbers in SheetA to column E SheetB, column E SheetC, and E in SheetD. Then delete any rows in SheetA that matches accounts in SheetB, SheetC, and SheetD. Can someone help me with a code for this?

 

[Mike820]

Thanks I believe this works. How would I include it to check another sheet witch account #s in column B?

 

[Fluff]

Like

Sub Mike820()
   Dim Ws As Worksheet
   Dim Dic As Object
   Dim Cl As Range, Rng As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Ws In Sheets(Array("SheetB", "SheetC", "SheetD"))
      For Each Cl In Ws.Range("E2", Ws.Range("E" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = Empty
      Next Cl
   Next Ws
   With Sheets("SheetE")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("SheetA")
      For Each Cl In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then
           If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
      If Not Rng Is Nothing Then Rng.EntireRow.Delete
   End With
End Sub

 

[Mike820]

Perfect. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mike820]

Hi Fluff, I’m hoping you could help me build on the script you provided recently. (#1-8 below is to be executed before the code you provided in your previous post).

1) in column AA SheetA any “0” change to blank cell

2) column AC SheetA some cells say “Fired - xxxx” , I’d like to change anything that doesn’t start with “Fired” changed to a dash then delete the word “Fired” from the text string so the text begins with a the dash.

3) column column AD and AE SheetA change blanks to dash

4) Column AL SheetA if any numbers are in here, change cells to “YES” if cells has “#N/A” clear it out. (Btw, the #N/A’s in this columns are not errors they’re hard coded)

5) I need to compare values in Column AF in SheetA with values in column AF in SheetB using account numbers (column E in both sheets). If an account has the same value (meaning column AF is the same) in both sheets then it is OK, but if the values are different then copy Z-AG in SheetA and paste it in column Z-AG in SheetB for that account.

6) If column P SheetA is less than 1 then change AB to ”<YEAR”

7) clear AA SheetA if AB is “<YEAR”

8) finally here I would use your code you previously posted to delete rows.

9) copy cells with values excluding headers in 1st row and insert in SheetB, above the second row (1st row has headers)

 

[Mike820]

I forgot 1 other item that can go anywhere in the above steps, if an account in column E of SheetA begins with “33”, delete the entire row.

 

[Mike820]

Steps 6 and 7 got cutoff, it should have read:

6)if column P SheetA is less than 1 the change AB to “greater than year”
<year
<year”
7) clear text in AA SheetA if AB says “greater than year”</year”
</year>

 

[Fluff]

This is now looking like a complete project. I would suggest that you either consider hiring a consultant (see https://www.mrexcel.com/consulting-services/).
Or alternatively start a new thread asking for help on one of your requests. When that is resolved start another new thread for another request, etc.