Loop and Index Problem

Hap

Well-known Member
I have a loop that is trying to remove duplicate values in a collection.

As in:

For Each iitem In newArea.theCollection
For q = 2 To newArea.theCollection.Count
If iitem.X = newArea.theCollection(q).X Then
If iitem.Y = newArea.theCollection(q).Y Then
newArea.theCollection.Remove(q)
End If
End If
Next q
Next iitem

The problem is that the For loop containing q as the increment apparently does not recheck the value of newArea.theCollection.Count at each increment. So I get an out of range error after an item has been removed.

Hopefully the dilemma is clear.

Any help would be appreciated.

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Greg Truby

MrExcel MVP
Loop backwards
for q = .Count to 2 step -1
(note implied use of WITH which would make object reference resolution quicker.)

Hap

Well-known Member
Actually it appears that I am going to run into another problem with this loop. When the iitem index is the same as q then the values will always be the same and try to remove itself.

I am trying to compare the two individual property values of each iitem to every other iitem in the collection and remove only the duplicates.

There is an easy solution here somewhere.

Hap

Well-known Member
I tried this:

For Each iitem In newArea.theCollection
For q = newArea.theCollection.Count To 1 Step -1
d = 0
If iitem.X = newArea.theCollection(q).X And iitem.Y = newArea.theCollection(q).Y Then
d = d + 1
End If
If d > 1 Then
newArea.theCollection.Remove (q)
End If
Next q
Next iitem

But this is not removing the duplication when checking the first two index values.

Greg Truby

MrExcel MVP
Code:
``````for i = .count-1 to 1 step -1
for j = .count to i+1 step -1
compare / remove
next j
next i``````
Would do it, I'd think (not tested). But I get a bad vibe from this code. Normally one of the key benefits of a collection is that it does not allow duplicates from the get-go. The very fact that you wound up with duplicates seems to indicate a greater problem to me...

Hap

Well-known Member
Here is the solution:

For d = theCollection.Count To 2 Step -1
For q = d - 1 To 1 Step -1
If theCollection(d).X = theCollection(q).X And theCollection(d).Y = theCollection(q).Y Then
theCollection.Remove (d)
End If
Next q
Next d

Points are added to the collection from other collections and arrays. I could test for duplicates at that time but it seemed simpler to check only once after all the points were in.

Replies
1
Views
78
Replies
4
Views
136
Replies
3
Views
87
Replies
0
Views
59
Replies
3
Views
53