I think that there are a couple of things that could go wrong with your sub.

How big is the range? How interspersed are the duplicates? If that range of
specialcells exceeds 8192 noncontiguous cells, then excel burbs without a
warning.

You maybe able to sort it, then delete the rows with duplicates.

This has a link that describes the problem:

http://support.microsoft.com/?kbid=832293
The Excel VBA function ".SpecialCells(xlCellTypeBlanks)" does not
work as expected

(but the xlcelltypeblanks was too specific.)

Another problem may be that the worksheet was filtered already.

I'd add this line right before the .autofilter line:

..AutoFilterMode = False

Like:

Option Explicit
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"
.AutoFilterMode = False
.Columns("B:B").AutoFilter Field:=1, Criteria1:="Duplicate"
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
End With

End Sub

If these aren't it, maybe you could run the code by stepping through it (hit F8
while in the sub).

After each step go back to excel and see if it looks like you want.


erin wrote:

Somehow it's not deleting the matched row(s). I have
absolutely no idea what happened. I did multiple tests
with dummy data and everything worked fine. Then I filled
in real data YTD and it weirded out on me. The sub is
still running (ColB data is disappearing, which it did
before). I just can't figure out what happened! I spent
hours going back to older files and trying to work forward
again.

-----Original Message-----
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!


.


--

Dave Peterson