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?

 

[My Aswer Is This]

You said:

I have account numbers in E2 of SheetA

Do you mean account numbers in Column E stating in row(2)

Or do really mean just in Sheet(1) Range("E2")
And when you say SheetA

Is the sheet really named SheetA ?
Or do you mean sheet(1) and Sheet(2)

​

 

[Mike820]

The account numbers start in E2 in all sheets, there are headers in the first row. The sheets are not actually names SheetA, SheetB etc. I only refer to them as such to distinguish from the different sheets.

 

[My Aswer Is This]

So assuming you have the number 5678
in Range("E67") of sheet(1)

You want the script to look down column E of sheets(2) and Sheets(3) and Sheet(4) for the value 5678

And if found on any of these sheets delete row(67) of Sheet(1)

Is this what you want?

 

[Mike820]

Correct

 

[Fazza]

It would be good to better understand the set up, and the volumes of data as perhaps there are better ways to do the task.

Maybe this helps you?
put this formula on sheetA

=SUMPRODUCT(COUNTIF(INDIRECT({"SheetB","SheetC","SheetD"}&"!e2:e1000"),E2))

and then when the result is > 1 delete rows. cheers

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Fazza]

ERROR in what I typed: should be if result is greater than zero.

(so maybe just a simple filter on the column with the offered formula, for values > 0, then select all & delete rows. for small data volumes should be OK)

 

[Mike820]

Thanks Fazza but I’d like to use vba for this task.

 

[Fluff]

How about

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("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