delete duplicates
 

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

delete duplicates
 

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

---
Regards,
Norman



"delmac" wrote in message
...
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.
--
delmac

delete duplicates
 

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


---
Regards,
Norman



"Norman Jones" wrote in message
...
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

---
Regards,
Norman



"delmac" wrote in message
...
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.
--
delmac