PDA

View Full Version : solved: Excel VBA Removing duplicates



swede
03-23-2009, 08:06 AM
Hi there!

I am trying to work a spreadsheet using Microsoft Excel 2003.

The spreadsheet has 8 columns and about 700 rows of data (text) Columns are A to H.
Three of the columns contain data that can occur multiple times. Those are columns B, C and F.

In case the data in any particular row in columns B, C and F (not or) are identical I want the entire duplicate row to be deleted.

I tried creating a macro that would do that, starting at row 2 since the spreadsheet has a title row. However all I receive is the error message "Error 13 - Type Mismatch".

Here is the code I tried using:

-------------------------------------------------------

Sub RowDelete()
Dim currRow As Integer
currRow = 2
Do Until Cells(currRow, 1) = ""
If Cells(currRow, "F") = Cells(currRow + 1, "F") And _
Cells(currRow, "B") = -Cells(currRow + 1, "B") And _
Cells(currRow, "C") = -Cells(currRow + 1, "C") Then
Range(Cells(currRow, "A"), Cells(currRow + 1, "H")).EntireRow.Delete
Else
currRow = currRow + 1
End If
Loop
End Sub


---------------------------------------------------

I would be greatful if there is any way to solve this issue.
Thanks in advance for any help to solve this.

Regards

Chris

Bob Phillips
03-23-2009, 08:17 AM
Sub RowDelete()
Dim currRow As Long
Dim lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For currRow = lastRow To 1 Step -1

If Cells(currRow, "F") = Cells(currRow - 1, "F") And _
Cells(currRow, "B") = Cells(currRow - 1, "B") And _
Cells(currRow, "C") = Cells(currRow - 1, "C") Then

Rows(currRow).Delete
End If
Next currRow
End Sub

swede
03-23-2009, 11:57 PM
wow thanks a LOT. works like a charm!:thumb
At least I am happy I wasn't on the entirely wrong track...