Duplicate remover
 

Hey everyone, I need to write a macro that checks if cell a2 is equal to
cell a3, and if so, deletes the entire row (the one that a2 is part
of).
I need this to run down about 1000 cells and continue to check and
delete, so i need it to be something like
if a2=a3 then 2:2 delete, if a3=a4, then 3:3 delete and so on, but is
there a way to automate this so you can assign the macro to a button,
and when you click it it goes through the list checking and deleting
rows?
Thanks guys


--
carlito_1985
------------------------------------------------------------------------
carlito_1985's Profile: http://www.excelforum.com/member.php...o&userid=21390
View this thread: http://www.excelforum.com/showthread...hreadid=399360

Duplicate remover
 

Assuming your data is the range A1:A1000:

Sub DeleteRow

For i = 1000 to 1 Step-1
If Cells(i,"A")=Cells(i,"A").offset(-1,0) then
Cells(i,"A").EntireRow.Delete
End if
Next

End sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=399360

Duplicate remover
 

If you are not sure of the number of rows in Column A, use:

Sub DeleteRow2()

numRows = Range("a65536").End(xlUp).row

For i = numRows to 1 Step-1
If Cells(i,"A")=Cells(i,"A").offset(-1,0) then
Cells(i,"A").EntireRow.Delete
End if
Next

End sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=399360

Duplicate remover
 

thanks guys, this is exactly what I was after, except that on both
formulas, they come up with errors at the end saying run-time error
'1004'
application-defined or object-defined error.
Any idea to what the cause may be?
Thanks again though,
Regards,
Andrew


--
carlito_1985
------------------------------------------------------------------------
carlito_1985's Profile: http://www.excelforum.com/member.php...o&userid=21390
View this thread: http://www.excelforum.com/showthread...hreadid=399360

Duplicate remover
 

I would imagine if the idea is to get rid of duplicates, he would need
to first sort out the data and then run the code.


David


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=399360

Duplicate remover
 

To suppress the error message, use:

Sub DeleteRow2()

numRows = Range("a65536").End(xlUp).Row
ON ERROR RESUME NEXT
For i = numRows To 1 Step -1
If Cells(i, "A") = Cells(i, "A").Offset(-1, 0) Then
Cells(i, "A").EntireRow.Delete
End If
Next

End Sub

The error comes about when the code reaches Row1 and is vainly
searching for the next Row above it through the Offset(-1,0). Since
there is none, Excel whimpers! The On error Resume Next qualification
suppresses the error message.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=399360