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!

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!

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!


.

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

Hi,
I tried the following suggestion to no avail. Good to
know re exceeding 8192 (although, I'm not there yet!).

After many more hours searching boards, I found the
following by Chip Pearson:

Sub DeleteDuplicateRows()

Dim Col As Integer
Dim R As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range


Col = ActiveCell.Column

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If


N = 0
For R = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(R, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns
(1), V) 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If

Application.StatusBar = "Row: " + Format(R)
Next R

End Sub

It takes a while to run, so not as efficient as the
previously working sub (it's just driving me nuts not
knowing why it stopped!!) If I wasn't so frustrated when
I finally found this, I might've been amused by the fact
that I searched his site much earlier today (using the
*wrong* key words "text match" and "delete").

I already foresee a problem with the ActiveCell defining
where match occurs. Ah well, enough for today... here's
hoping I don't spend the night dreaming about it too :-)

-----Original Message-----
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.Delete
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

.