CODEWITHSUNDEEP
excelvba
JcDiago
JcDiago

Reputation: 11

Checking for duplicates across all cells

How do I code this in the most simplest way?

If let's say Range("A1").value = "Thursday"

Check for duplicates on all the cells which has value in them (B1, C1, D1,...)

If a duplicate is found, select 3 cells below each of those cells Range("B2:B4") and so on...

Upvotes: 1

Views: 15316

Answers (2)

Ravi Shankar Kota
Ravi Shankar Kota

Reputation: 705

Below code identifies duplicate value in a column and highlight with red. Hope this might be of some help.

  iLastRow = Cells(chosenExcelSheet.Rows.Count, 1).End(xlUp).Row 'Determine the last row to look at     
    Set rangeLocation = Range("A1:A" & iLastRow) 'Range can be updated as per your need

    'Checking if duplicate values exists in same column
        For Each myCell In rangeLocation
            If WorksheetFunction.CountIf(rangeLocation, myCell.Value) > 1 Then
                myCell.Interior.ColorIndex = 3'Highlight with red Color
            Else
                myCell.Interior.ColorIndex = 2'Retain white Color
            End If
        Next

Upvotes: 0

user6432984
user6432984

Reputation:

The simplest way is to check for duplicates:

Using a WorksheetFunction

=COUNTIF(A:A,A1)>1

enter image description here

Using the VBA

Dim Target As Range
Dim r As Range

Set Target = Range("A1", Range("A" & Rows.Count).End(xlUp))

For Each r In Target
    r.Offset(0, 1) = WorksheetFunction.CountIf(Target, r.Value) > 1
Next

If you want to remove duplicates in the first column of the range

Target.RemoveDuplicates Columns:=1, Header:=xlNo

If you wanted to expand your range to include Columns B and C

Set Target = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)

Remove duplicates in the first and third columns

Target.RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo

Remove rows that contain the duplicates

Target.EntireRow.RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo

Upvotes: 4

Related Questions