I'm trying to use the following macro (which I got from this forum) to delete
all rows in which cell values are duplicates in cols B, F, and J. Column B
contains a date, col F contains text, and column J contains text. When I run
the macro I get an error at the ".Rows(r).Delete shift:=xlUp" line. The
error is "Delete method of Range class failed." At the time of the error, r
= 4880 (a row with data in all three cols being compared). Can anyone help
debug? Here's the code:

Sub DeleteDupes()
Dim ws1 As Worksheet
Dim lastrow As Long
Dim r As Long

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set ws1 = Worksheets("Combined Notes")
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.And(.Cells(r, "B") = .Cells(r - 1, "B") _
, .Cells(r, "F") = .Cells(r - 1, "F"), .Cells(r, "J") = _
.Cells(r - 1, "J")) Then
.Rows(r).Delete shift:=xlUp
End If
Next r
End With

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Looks like you are mixing Excel language with VBA. Try this.

For r = lastrow To 2 Step -1
If .Cells(r, 2) = .Cells(r - 1, 2) And .Cells(r, 6) = .Cells(r - 1, 6) _
And .Cells(r, 10) = .Cells(r - 1, 10) Then
.Rows(r).Delete
End If
Next r


"Heliocracy" wrote:

I'm trying to use the following macro (which I got from this forum) to delete
all rows in which cell values are duplicates in cols B, F, and J. Column B
contains a date, col F contains text, and column J contains text. When I run
the macro I get an error at the ".Rows(r).Delete shift:=xlUp" line. The
error is "Delete method of Range class failed." At the time of the error, r
= 4880 (a row with data in all three cols being compared). Can anyone help
debug? Here's the code:

Sub DeleteDupes()
Dim ws1 As Worksheet
Dim lastrow As Long
Dim r As Long

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set ws1 = Worksheets("Combined Notes")
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.And(.Cells(r, "B") = .Cells(r - 1, "B") _
, .Cells(r, "F") = .Cells(r - 1, "F"), .Cells(r, "J") = _
.Cells(r - 1, "J")) Then
.Rows(r).Delete shift:=xlUp
End If
Next r
End With

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub