Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete duplicates | Excel Discussion (Misc queries) | |||
Delete duplicates | Excel Worksheet Functions | |||
Delete duplicates? | Excel Discussion (Misc queries) | |||
Delete duplicates? | Excel Discussion (Misc queries) | |||
Delete Duplicates | New Users to Excel |