VBA Dynamic way to copy and paste unique values

Hi,

I have a ever changing column of data (in no of rows). The starting point is given to C2. In this column I have the following values to pick a few:

BB
NT
BB
NO
CB
LE
WO
WO

What I want to do is consolidate these values and paste under the existing values in column C (previously Ive used Selection.End(xlDown).Offset(1, 0).Select to find the end.

My problems are the following:
1. Consolidate does not work since it needs to specify my workbooks directory and sheet which will be changing from user to user and the sheet is created in the macro so it is not given a specific name.
2. Advance filter copy Uniques does not work for some reason, I get duplicates trying to use this function (these duplicates does not appear when i manually try consolidation).

As I said I can not specify a certain range however would work to choose entire C:D since rest is empty.

Does anyone have a good idea how to solve this?

Many thank you! I am sooooo stuck

I'm not for sure I understood your request.
If I did, then:
Backup your data.
Copy the code below to a standard module (if you don't know how, ask).
Run: Run_GetUniqueValues

It was unclear to me whether you wanted a list of all unique values from both columns C & D (or just C).

Thus in the code below you will see both:

Please Login or Register  to view this content.

For both C & D, remove the apostrophe and add it to the line above.

The function: GetUniqueValues collects unique values from any range and stores it in a string separated by the vbCR character. Then Run_GetUniqueValues puts these values in a string array.

You didn't mention if you wanted these values sorted. I added a Bubble sort function to sort these values.

Then leaving an empty row, I copied these values below the previously last row of data in column C.

Please Login or Register  to view this content.

Hi Steven,

Thank you so much for your reply. Late last evening I actually made it work using the following:

Please Login or Register  to view this content.

It is definately not the most effective way but for my purpose it works fine

Thank you!

Joakim

This is a little more efficient:

Please Login or Register  to view this content.

The problem with that kyle is that I dont want to remove the duplicates from the original data set, hence my copy into new area. I want to keep them in the original table just creating a "new set" with unique values so I can SUMIF from the above. (Yes it seems awkward to do it this way but the accounting system demands the format to be in this way)

Joakim

No probs, just switch the code round a bit

Please Login or Register  to view this content.

This is helpful. However, if there was a third column with different numeric values, how would I have it sum the values from that column in all of the duplicate rows into the consolidated one?

Dear StevenM
Is is possible to sort the values based on alphanumerically by using your Function BubbleSortsArray(ByRef sArray() As String) fucntion.


Thanks and regards,

Sudhakar

Your