delete duplicates

[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.

 

[Norman Jones]

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("A2100") '<<===== 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("A2100")
with:
Set rng = sh.UsedRange

 

[Norman Jones]

Hi Delamc,

Change:

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

to

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

to correct missing 's' in "Duplicates".