Try to search your question here, if you can't find : Ask Any Question Now ?

Excel VBA – How to automate check and select duplicates in multiple lists in multiple sheets in one workbook?

HomeCategory: stackoverflowExcel VBA – How to automate check and select duplicates in multiple lists in multiple sheets in one workbook?
Avatarpushpa asked 4 months ago

How to automate check for duplicates in multiple lists in multiple sheets in the same workbook?

I’ve managed to make it work semi-automatically by having a macro for each comparison that I need. That means a macro for finding duplicate values between:

Worksheets(“All”).Range(“Annie”) and Worksheets(“Annie”).Range(“A:A”)
Worksheets(“All”).Range(“Benny”) and Worksheets(“Benny”).Range(“A:A”)
etc.
etc..

Here is a screenshot that shows where I’m at, and hopefully visualizes what I am trying to do

(Annie’s been to Sweden and Kenya – therefore those countries are marked red by the macro)
(Benny’s been to Russia and Spain)
(And so forth)

It would be much more efficient to make just ONE code, that does this for all columns that has values in Worksheets(“All”). I need to be able to have at least 50 columns with names (Annie, Benny, Charlie, Danny, Emma, Fabian, etc., etc..)

Is this possible?

Here is my code, so far (for “Benny”):

Sub findDuplicatesBenny()

Dim rng1, rng2, cell1, cell2 As Range

Set rng1 = Worksheets("All").Range("Benny")

Set rng2 = Worksheets("Benny").Range("A:A")

For Each cell1 In rng1
If IsEmpty(cell1.Value) Then Exit For

For Each cell2 In rng2
If IsEmpty(cell2.Value) Then Exit For
If cell1.Value = cell2.Value Then

cell1.Interior.ColorIndex = 22
cell1.Interior.Pattern = xlSolid
End If

Next cell2
Next cell1

End Sub

Any help will be much appreciated!

1 Answers
Best Answer
AvatarMannu answered 4 months ago
Your Answer

17 + 6 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com