Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • 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 HensonIT & Database AssistantCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 HensonIT & Database AssistantCommented:
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 LissRetired ProgrammerCommented:
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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