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

bill151515

Board Regular
Joined
May 19, 2005
Messages
87
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
2 methods.

1 Put this at the top of the code.
Code:
Option Compare Text

2 Use this If statement.
Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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

Board Regular
Joined
May 19, 2005
Messages
87
thanks

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

using the UCAse() and looping from the bottom up

Thanks Alot!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top