VBA to check two columns for duplicate values

theishv

New Member
Joined
Jan 18, 2018
Messages
12
Hello!

I would appreciate help with some VBA code.
The code has to check two columns for duplicate values. both the values in the same row of the columns has to match the values in a row somewhere else in the columns. When such a match is found the bottom most row should be deleted.


To illustrate this i have made an example, in the picture below row 9 should be deleted.
dtWcnR

dtWcnR


29lyb1s.png


dtWcnR

Thank you.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi & welcome to the board.

Which 2 columns do you want to check & what row does the data start in?
 
Upvote 0
In that case try this
Code:
Sub DeleteDupes()

   Dim Cl As Range
   Dim ValU As String
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("D1", Range("D" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If Not .exists(ValU) Then
            .Add ValU, Nothing
         Else
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete

End Sub
This will work on the active sheet
 
Upvote 0
In that case try this
Code:
Sub DeleteDupes()

   Dim Cl As Range
   Dim ValU As String
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("D1", Range("D" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If Not .exists(ValU) Then
            .Add ValU, Nothing
         Else
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete

End Sub
This will work on the active sheet

Thank you very much!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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