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
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