Boromir45
09-22-2006, 04:32 AM
Hi,
I've got a spreadsheet which contains over 17500 rows of which there is some duplicate data which will eventually need to be deleted.
Short of spending hours and hours going through each row and checking it, I would like to be able to devise a macro in order to indicate which rows need deleting.
I've checked the forums and found this snippet but this only works for one column but I need it over several. It will also delete the column rather than hi-light it.
Option Explicit
Sub DeleteDups()
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub
The spreadsheet consists of a list of companies and associated Bosses, Directors and HR guys, half of which has been duplicated. I have sorted the data so the duplicates show underneath each other but there are literally hundreds of surplus rows.
So if the companies are in column A, the boss is in column B and the Bosses Position is in Column C. I need the macro to check each column before deciding it is a duplicate.
My skills in Excel are good but it's VBA where I have difficulties so if there is any suggestions you might have, it would be most appreciated.
Thanks in advance.
I've got a spreadsheet which contains over 17500 rows of which there is some duplicate data which will eventually need to be deleted.
Short of spending hours and hours going through each row and checking it, I would like to be able to devise a macro in order to indicate which rows need deleting.
I've checked the forums and found this snippet but this only works for one column but I need it over several. It will also delete the column rather than hi-light it.
Option Explicit
Sub DeleteDups()
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub
The spreadsheet consists of a list of companies and associated Bosses, Directors and HR guys, half of which has been duplicated. I have sorted the data so the duplicates show underneath each other but there are literally hundreds of surplus rows.
So if the companies are in column A, the boss is in column B and the Bosses Position is in Column C. I need the macro to check each column before deciding it is a duplicate.
My skills in Excel are good but it's VBA where I have difficulties so if there is any suggestions you might have, it would be most appreciated.
Thanks in advance.