Help with remove rows base on duplicate column value script

davidmg1982

Board Regular
Joined
Oct 12, 2015
Messages
54
The script below does a good work removing the rows based on the duplicates found in column a, however, it removes the last line with the duplicate, what I need is to remove the first line of the dups found. I appreciate your help.

Column A
Value 1
Value 2 <- Remove
Value 2
Value 3<- Remove
Value 4
Value 3

VBA Code:
Sub RemoveDupe()
Dim rCell As Range
Dim rRange As Range
Dim lCount As Long

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

For lCount = lCount To 1 Step -1
    With rRange.Cells(lCount, 1)
        If WorksheetFunction.CountIf(rRange, .Value) > 1 Then
            .EntireRow.Delete
        End If
    End With
Next lCount
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,631
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub davidmg()
   Dim Rng As Range
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
         If Not .Exists(Cells(i, 1).Value) Then
            .Add Cells(i, 1).Value, Nothing
         Else
            If Rng Is Nothing Then Set Rng = Rows(i) Else Set Rng = Union(Rng, Rows(i))
         End If
      Next i
   End With
   If Not Rng Is Nothing Then Rng.Delete
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,631
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,730
Messages
5,638,031
Members
417,000
Latest member
JasonWilliam

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
Top