deleting rows based on text in a cell - not case sensative

[bill151515]

i have a column of data that has been presorted ascending.
i have a macro that goes down the column and deletes any duplicate rows


'code'

Set currentcell = Worksheets("IDSort").Range("A1")
Do While Not IsEmpty(currentcell)
Set nextcell = currentcell.Offset(1, 0)
If nextcell.Value = currentcell.Value Then
currentcell.EntireRow.Delete
End If
Set currentcell = nextcell
Loop



this is working fine except that i have some instances of ids that are duplicates (and need to be deleted) but the case is different ex.. 123xx56, 123XX56

is there a way to modify what I have and not have it be case sensative so that 1 of those would be deleted?? or a better way altogether to accompish this???
123

 

[Norie]

2 methods.

1 Put this at the top of the code.

Option Compare Text

2 Use this If statement.

If UCase(nextcell.Value) = UCase(currentcell.Value) Then

You could also look into using Advanced Filter to return only unique records.

To help with that we would need more information on the actual data.

 

[HalfAce]

Something else to consider too...
If you will ever have more than two consecutive rows that meet the delete criteria then looping from the top down like that won't cut it.
Picture this. You have dupes in rows 4, 5 & 6.
Your loop has reached row 4, sees a dupe in row 5 so it deletes row 4. (as it should.)
Row 5 has just become row 4 and row 6 has just become row 5.
Excel has already checked row 4 against row 5 so it will move on to row 5 and check it against row 6. these do not match so it moves on.
You're left with matches in rows 4 & 5.

You can solve this one of a couple ways. Either (1) loop from the bottom up or (2) you can store the rows to be deleted for now and delete them all at once outside the loop.

 

[bill151515]

thanks

I used a combo of both your replies ....

using the UCAse() and looping from the bottom up

Thanks Alot!!!