Attn Juan Pablo, ty very much for the macro... I think I'm using it incorrectly?

Posted by Eric on October 08, 2001 6:23 AM

Sorry to have failed to respond to your macro post (689.html) more promptly, but I had a hard drive crash on Saturday :-( and am just now back online.

I suspect my problem with the macro is with the input ranges. I am holding the ctrl key down to select different columns I want to combine (each one has a row title), and after I select a destination cell and say "OK" I get a red X message with the number 400. If I say OK to that then I get all three of the ranges I selected displayed in one column (the first column that I selected). What follows is the dummy data on which I tried this:
{"apples","peaches","oranges";"a","b","c";"b","c","d";"c","d","e";"d","e","f";"e","f","g";"f","g","h";"g","h","I"}

I started the macro and selected the following as ranges to join:
$A$1:$A$8,$B$1:$B$8,$C$1:$C$8

then input $D$1 (or D1- same result) as the destination cell, and I get the 400 message, click OK, and get the following result:
{"apples";"a";"b";"c";"d";"e";"f";"g";"b";"c";"d";"e";"f";"g";"h";"c";"d";"e";"f";"g";"h";"I"}

Any tips on what I'm doing wrong?

Posted by Juan Pablo on October 08, 2001 6:48 AM

Ok, i tried to do the macro that works, as you noticed, with different ranges. Let's assume this, for example.

Case 1:
{"Fruits";"Apple";"Banana";"Apple";"Grape";"Orange";"Peaches";"Apple";"Grape";"Peaches";"Orange";"Banana"} in A1:A12

Case 2:
{"Fruits","Fruits","Fruits";"Apple","Apple","Orange";"Banana","Grape","Peaches";"Apple","Orange","Apple";"Grape","Peaches","Grape";"Orange","Apple","Peaches";"Peaches","Grape","Orange";"","Peaches","Banana";"","","Apple";"","","Grape";"","","Peaches";"","","Orange";"","","Banana"} in C1:E13

Now, run the macro.
First range: A1:A12 (By the way, i don't get any error messages)
Destination: G1
I get
{"Fruits";"Apple";"Banana";"Grape";"Orange";"Peaches"}

Now, again, run the macro.
First range: C1:C7,D1:D8,E1:E13
Destination: H1

I also get
{"Fruits";"Apple";"Banana";"Grape";"Orange";"Peaches"}

Now, your example...
When i put your range and run the macro i get this:
{"apples";"a";"b";"c";"d";"e";"f";"g";"h";"I"}

What the macro does is copy each range (A1:A8, B2:B8 and C2:C8) in a different range, Advanced Autofilter it with Unique entries and copy the results to the destination cell.

I hope this clarifies what the macro does and i hope it helpes.

Juan Pablo

Posted by Eric on October 08, 2001 9:50 AM

Thx Juan Pablo, your macro is beautiful, my pasting...not so much

worked fine after I pasted it into the correct module, D'OH!