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

Mike820

New Member
Joined
Jul 24, 2019
Messages
21
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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)

 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
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)
 
Upvote 0
How about
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top