Extract Duplicates

[Sham Hyder]

Hello Experts,

Greetings from Malaysia...

I am new to VBA programming and am stumped with this problem. I have attached a workbook with the code I have written.

Basically what is needed is to extract duplicates and highlight them in the original range. I know this can be achieved with Conditional Formatting (CF), but in future there'll be a need to check for permutations of the numbers which CF cannot handle.

I believe its a "looping" issue that I have. Can't seem to get my head around it. Any help is greatly appreciated.

Thank you.

Warm regards
Sham Hyder

 

[Nebu]

Hi:

Use the following code.

Private Sub CheckForDups_Click()
Application.ScreenUpdating = False

Dim rng As Range

With Sheet16

j& = .Cells(Rows.Count, 2).End(xlUp).Row
k& = .Cells(8, Columns.Count).End(xlToLeft).Column
Set rng = .Range("B8:F" & j)
rng.Interior.ColorIndex = xlNone
rng.Font.ColorIndex = 0

For i& = 8 To j
    For c& = 2 To k
        cnt% = WorksheetFunction.CountIf(rng, .Cells(i, c))
        If cnt > 1 Then
            .Cells(i, c).Interior.ColorIndex = 6
            .Cells(i, c).Font.ColorIndex = 1
        End If
    Next
Next
End With
Application.ScreenUpdating = True
End sub

Thanks

 

[Sham Hyder]

Hi Nebu,

Thank you for your response.

In my haste with my initial question, I forgot to state that the duplicates to be highlighted should be within each column.

Eg
Only values in Column B are checked and highlighted for duplicates. In my workbook for example, 2000 in column B should not be highlighted as it is not duplicated in column B. With your code it is being highlighted because 2000 appears in columns D and E.

Then it checks column C and so on. which means duplicates within the column are highlighted.

Hope you are able to modify your code accordingly for me.

Thank you.

Regards
Sham Hyder

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Sham Hyder]

Hi Nebu,

Thank you so much.

Now to get to work. And to figure out the permutation bit.
Will post if I hit a wall...

Thanks again

Regards
Sham Hyder