it's not working anymore, please help!
What is happening, how does it not work?
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"erin" <erin@wdprosdotcom wrote in message
...
Hi,
I'm copying weekly sales quote info from one workbook to
a running master list in another. Each job name is
unique (appears in column A of master). I want to ensure
that no duplications occur, so need to see if a match
exists and if so, automatically delete it.
The following was working just fine (during multiple
tests) and now it's not!!!!
Sub DeleteDuplicates()
Dim cLastRow As Long
With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B1").FormulaR1C1 = "=IF(COUNTIF
(R2C1:RC1,RC[-1])1,""Duplicate"","""")"
.Range("B1").AutoFill Destination:=.Range
("B1", .Cells(cLastRow, "B")), Type:=xlFillDefault
.Range("A1").EntireRow.Insert
.Range("B1").FormulaR1C1 = "Test"
.Columns("B:B").AutoFilter Field:=1,
Criteria1:="Duplicate"
.Cells.SpecialCells
(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
Facts: Job name (to be matched) appears in ColA. Sheet
runs ColA through ColQ. Job listings begin row 3 and run
through row 146. Totals appear in rows 147-150.
Thanks much!
|