Solved

For loop on unique records in a list?  VBA

Posted on 2015-01-27
15
59 Views
Last Modified: 2016-02-10
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
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +3
15 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40573136
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40573147
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
 

Author Comment

by:RWayneH
ID: 40573155
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:RWayneH
ID: 40573171
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40573180
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40573220
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40573224
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40573227
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
 

Author Comment

by:RWayneH
ID: 40573232
It is skipping over the MsgBox cel  line.  Makes it hard to follow were it is.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40573269
Here is the file
ActUniqe.xls
0
 

Author Comment

by:RWayneH
ID: 40573306
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
 

Author Comment

by:RWayneH
ID: 40573403
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40573480
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40574486
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
 

Author Closing Comment

by:RWayneH
ID: 40596004
Thanks for the help.  Worked great!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question