Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
Thank you very much, Paul. I think you got me an accurate solution. Thanks -Dileep |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
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 wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue with loop
Thank you Paul. I think this will work . I appreciate your timely help. Regards -Dileep |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For next do loop issue | Excel Discussion (Misc queries) | |||
Loop through range issue | Excel Programming | |||
do ... until loop issue | Excel Programming | |||
Issue with Do Loop | Excel Programming | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming |