VBA: Pull unique text records from multiple columns and store into array?

Hi all,

Please bear with me - I'm a typical Dr. Frankenstein when it comes to VBA; I splice and paste together snippets I scavenge across the net. Usually I eventually understand what I'm creating but I've ran into a problem here.

What I want to accomplish: a function that pulls unique records (text) from a number of predefined columns in a range of arbitrary size and stores it in a variable for later use.

What I've got now: a function that pulls unique records from predefined column #1 and pastes them into a cell M10 and after that pulls the unique records from predefined column #2 and pastes them over cell M10. Not that useful yet.

It seems simple enough to just put the unique records found in predefined column #1 in a "queue" while the function fetches the unique records in column #2 and join the two, but my trial'n'error-efforts with another loop have yet amounted to nothing.

Any help with this would be greatly appreciated so many thanks in advance!

PS: I'm guessing that the "Scripting Dictionary" requires the user to install a Microsoft add-on, so if that's the case a work-around without that method would be desirable.

Please Login or Register  to view this content.

The "Scripting Dictionary" is not an add-on, but a reference to a code module. Once you set it, the user will be unaware of it UNLESS that don't have it installed on their system.

We can use the normal Collection object to get our unique list without setting a reference.

Are the column headers("<Account>", "<Party account>") always in 1-2 position, or do we need to search for them?

Oh wait. I should've posted the function

I assumed as much. We won't need that. The collection will handle that for us.

With this code (untested BTW since I didn't have a sample sheet to work from), you end up with an array named MyAry that has a list of all the unique items in
those columns. You'll have to decide how to proceed next. Continue with code. Or make the array Public. Or push it to the sheet.

Please Login or Register  to view this content.

You'll get that error if the Unique.Count = 0.