delete duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI all, I've got a spreadsheet where I have many duplicates rows. I've got a
cell in each row marked DUPLICATE and would like to run a macro to delete
them from the spreadsheet. Thanks.
 
Hi Delmac,

One way:

Sub Tester()
Dim rng As Range
Dim sh As Worksheet
Dim lRow As Long
Dim i As Long
Const col As String = "D" '<<===== CHANGE

Set sh = ActiveWorkbook.Sheets("Sheet2") '<<===== CHANGE
Set rng = sh.Range("A2:D100") '<<===== CHANGE


lRow = rng.Rows(rng.Rows.Count).Row

With sh
For i = lRow To 2 Step -1
If .Rows(i).Cells(1, col).Value = "Duplicate" Then
.Rows(i).Delete
End If
Next
End With

End Sub

Change the value of col from "D" to the column containing "Duplicates".
Change the name of the workbook.worksheet to suit your requirements.

Change the value of range to suit. To operate on the whole sheet, replace:

Set rng = sh.Range("A2:D100")
with:
Set rng = sh.UsedRange
 
Hi Delamc,

Change:
If .Rows(i).Cells(1, col).Value = "Duplicate" Then
Click to expand...

to

If .Rows(i).Cells(1, col).Value = "Duplicates" Then

to correct missing 's' in "Duplicates".
 
Back
Top