• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

For loop on unique records in a list? VBA

Is there a way to run a procedure only on the unique records in a list without copying them out removing dupicates?
I have list in column AD and it can have random duplicate records within the list.  At first approach I was going to copy them out removing dups, but thought I would ask.
0
RWayneH
Asked:
RWayneH
  • 6
  • 4
  • 2
  • +3
1 Solution
 
Saqib Husain, SyedEngineerCommented:
You can use the "Collection" object to collect unique objects. Subsequent entries of the same object will generate an error. This error can be trapped and ignored.
0
 
Rob HensonFinance AnalystCommented:
Are they in order?

If so, you can set the procedure to check each record as it comes to it as to whether it is the same as the previous record.

If not, I guess you could copy the record to a supplementary list as it gets dealt with and then have the procedure check the list to see if that record has already been dealt with.

Thanks
Rob H
0
 
RWayneHAuthor Commented:
Ok.. Do you have an example of this? or can we create a simple one?  Maybe for each unique record enter a MsgBox with the value in it?  Go through the whole list and exit after the last one?   I have never used this before.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
RWayneHAuthor Commented:
No, these will not be in order... I wish they were...  it is not an option to sort these..   I need to keep records in the original order..  which probably makes it more difficult.  Sorry
0
 
Saqib Husain, SyedEngineerCommented:
Enter a list in D1:D13 and try this macro

Sub actuniqe()
Dim coll As New Collection
For Each cel In Range("D1:D13")
    On Error GoTo ext
    coll.Add cel, cel
    MsgBox cel
ext:
    On Error GoTo -1
Next cel
End Sub
0
 
Patrick MatthewsCommented:
You can also use a Dictionary.  I write a comprehensive article on the subject here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

A Dictionary offers more flexibility and functionality than a Collection, and my testing suggests it can be substantially faster (although you would never notice the difference on small data sets).
0
 
Rob HensonFinance AnalystCommented:
You could add a row identifier in numerical order, sort by duplicated records, run routine and then sort back to original order using row identifier.
0
 
Martin LissOlder than dirtCommented:
Or record a macro using Filter to remove duplicates, then use that code in a loop of your own where Cells(somerow, 1).Entirerow.Hidden = False
0
 
RWayneHAuthor Commented:
It is skipping over the MsgBox cel  line.  Makes it hard to follow were it is.
0
 
Saqib Husain, SyedEngineerCommented:
Here is the file
ActUniqe.xls
0
 
RWayneHAuthor Commented:
The example file is working for me, not sure why it skipped over the MsgBox for me??  
Thanks, let me work with this and see if I can insert this into my procedure.
0
 
RWayneHAuthor Commented:
Instead of For Each cel In Range("D1:D13")
Could I use: For Each cel In Selection        or I was considering naming the range.  I have to exclude the header.
Trying to figure out how to set a dynamic list instead of a absolute value for the list.
0
 
Saqib Husain, SyedEngineerCommented:
For Each cel In Selection
For Each cel In Range("D2:D" & range("D2").end(xldown).row)
For Each cel In Range("D2:D" & range("D" & rows.count).end(xlup).row)

Are various options

If you can give your scenario an appropriate option could be suggested.
0
 
aikimarkCommented:
Patrick has posted the best recommendation (http:#a40573220 ) -- use a Dictionary object to get unique values.

Alternatively, use the AdvancedFilter to extract a unique list of items.  Hopefully, your list has a header row.

You can techniques from my Fast Data Push to Excel article to populate the AD cells' values from the dictionary's items collection.  You will need to use the Transpose worksheetfunction and be mindful that your original AD contents will not be changed by the shorter list/array push.
http:A_2253.html
0
 
RWayneHAuthor Commented:
Thanks for the help.  Worked great!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 6
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now