Loop and Index Problem

Hap

Well-known Member
Joined
Jul 20, 2005
Messages
647
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.
 

Some videos you may like

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
Joined
Jun 19, 2002
Messages
10,014
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
Joined
Jul 20, 2005
Messages
647
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
Joined
Jul 20, 2005
Messages
647
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
Joined
Jun 19, 2002
Messages
10,014
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
Joined
Jul 20, 2005
Messages
647
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,308
Members
412,716
Latest member
thviid
Top