Macro Issue with loop

[Dileep Chandran]

Hello everyone,

I got this macro, to delete duplicate data, from this newsgroup last
week.
I am facing an issue with the same as it start checking again from the
column1 once a duplicate is deleted. Can anyone help me to sort out
this issue?

The macro reads:

Sub deleteDuplicates()


Dim myName As Variant 'Name column
Set myName = Range("C2")


Do While Not IsEmpty(myName)


Set myAddress = myName.Offset(0, 1)
Set myCity = myName.Offset(0, 2)
Set myState = myName.Offset(0, 3)


Set nextName = myName.Offset(1, 0)
Set nextAddress = myAddress.Offset(1, 0)
Set nextCity = myCity.Offset(1, 0)
Set nextState = myState.Offset(1, 0)


If myName = nextName And _
myAddress = nextAddress And _
myCity = nextCity And _
myState = nextState Then
myName.EntireRow.Delete
End If


Set myName = nextName


Loop
End Sub


Thanks
-Dileep


End Sub

 

[paul.robinson]

Hi
I havn't checked this, but maybe assigning myName to a deleted row is
causung the problem. Try this slight variation of your code;

Sub deleteDuplicates()

Dim myName As Variant 'Name column
Set myName = Range("C2")


Do While Not IsEmpty(myName)


Set myAddress = myName.Offset(0, 1)
Set myCity = myName.Offset(0, 2)
Set myState = myName.Offset(0, 3)


Set nextName = myName.Offset(1, 0)
Set nextAddress = myAddress.Offset(1, 0)
Set nextCity = myCity.Offset(1, 0)
Set nextState = myState.Offset(1, 0)


If myName = nextName And _
myAddress = nextAddress And _
myCity = nextCity And _
myState = nextState Then
nextName.EntireRow.Delete
Else
Set myName = nextName
End If
Loop
End Sub

The IsEmpty property can be tricky too. I usually use

If Trim(myName.Value)<>""

incase there is none printing information in the cell.

regards
Paul

 

[Dileep Chandran]

Thank you very much, Paul. I think you got me an accurate solution.

Thanks
-Dileep

 

[Dileep Chandran]

Hi Paul,

While checking for duplicates, this macro is not considering data as
duplicate if one is in lower case and other is in upper case.

Can you help me out to overcome this issue?

-Dileep

 

[paul.robinson]

Hi
Try this. Untested and I'm a bit worried about the last Set myName
statement but havn't the time to check it.
regards
Paul
Sub deleteDuplicates()
Dim myName As Range 'Name column
Dim myData as String, nextData as String, i as integer
Set myName = Range("C2")

With myName
Do While Trim(.Value)<>""
myData = ""
For i = 0 to 3
myData = myData & Trim(lcase(.offset(0,i).Value))
next i
nextdata = ""
For i = 0 to 3
nextData = nextData & Trim(lcase(.offset(1,i).Value))
next i
'Check data
If myData = nextData then
.offset(1,0).EntireRow.Delete
Else
Set myName = .offset(1,0)
End If
Loop
End With 'myName
End Sub

 

[Dileep Chandran]

Paul,

I could not use this code bcoz I have inserted lots of other codes to
the module I have given above. So can you suggest me an addition to
that, which will check duplicates without considering the lower or
upper case?

Thank you for your timely reply.

-Dileep

 

[paul.robinson]

Hi
Can you work with this?

Sub deleteDuplicates()
Dim myName As Range 'Name column
Set myName = Range("C2")
Do While Not IsEmpty(myName)
Set myAddress = myName.Offset(0, 1)
Set myCity = myName.Offset(0, 2)
Set myState = myName.Offset(0, 3)
myData = ""
myData = Trim(lcase(myName.Value)) &
Trim(lcase(myAddress.Value)) _
& Trim(lcase(myCity.Value)) &
Trim(lcase(myState.Value))

Set nextName = myName.Offset(1, 0)
Set nextAddress = myAddress.Offset(1, 0)
Set nextCity = myCity.Offset(1, 0)
Set nextState = myState.Offset(1, 0)
nextData = ""
nextData = Trim(lcase(nextName.Value)) &
Trim(lcase(nextAddress.Value)) _
& Trim(lcase(nextCity.Value)) &
Trim(lcase(nextState.Value))
If myData = nextData Then
nextName.EntireRow.Delete
Else
Set myName = nextName
End If
Loop
End Sub

regards
Paul

 

[Dileep Chandran]

Thank you Paul. I think this will work .

I appreciate your timely help.

Regards
-Dileep