Macro to move multiple columns into one

[aka_krakur]

I posted this yesterday with no response; so I thought I'd try again and try and make my question a little more clear. Here are some examples of the data and then I'll ask my questions after the examples:

BEFORE

Book1
	A	B	C	D
1	Before
2	REPORTED_DEV_CLARIFICATION
3	1049 - Above;
4	1049 - Above;1069 - Shaft;1670 - Above RBP;
5	1049 - Above;1586 - Balloon;
6	1049 - Above;1586 - Balloon;1670 - Above RBP;
7	1049 - Above;1586 - Balloon;1670 - Above RBP;1586 - Tip;
8	1049 - Above;1586 - Shaft;1670 - Above RBP;1670 - Against Resistance;
9	1049 - Above;1670 - Above RBP;
10	1049 - Above;1670 - Above RBP;1528 - Balloon Catheter;1586 - Balloon;
11	1049 - Above;1670 - Above RBP;1586 - Balloon;
12	1049 - Above;1670 - Above RBP;1586 - Shaft;1586 - Balloon;
13	1049 - Below;
14	1049 - Below;1158 - Dislodged;
15	1049 - Below;1158 - Partial;1670 - Off Label Use;
16	1049 - Below;1528 - Guide Wire;
17	1049 - Below;1528 - Guide Wire;1670 - Off Label Use;
18	1049 - Below;1586 - Balloon;
19	1049 - Below;1586 - Shaft;
20	1049 - Below;1586 - Shaft;1670 - Off Label Use;
Sheet1

AFTER

Book1
	A	B	C	D
22	After
23	REPORTED_DEV_CLARIFICATION
24	1049 - Above
25	1049 - Above	1069 - Shaft	1670 - Above RBP
26	1049 - Above	1586 - Balloon
27	1049 - Above	1586 - Balloon	1670 - Above RBP
28	1049 - Above	1586 - Balloon	1670 - Above RBP	1586 - Tip
29	1049 - Above	1586 - Shaft	1670 - Above RBP	1670 - Against Resistance
30	1049 - Above	1670 - Above RBP
31	1049 - Above	1670 - Above RBP	1528 - Balloon Catheter	1586 - Balloon
32	1049 - Above	1670 - Above RBP	1586 - Balloon
33	1049 - Above	1670 - Above RBP	1586 - Shaft	1586 - Balloon
34	1049 - Below
35	1049 - Below	1158 - Dislodged
36	1049 - Below	1158 - Partial	1670 - Off Label Use
37	1049 - Below	1528 - Guide Wire
38	1049 - Below	1528 - Guide Wire	1670 - Off Label Use
39	1049 - Below	1586 - Balloon
40	1049 - Below	1586 - Shaft
41	1049 - Below	1586 - Shaft	1670 - Off Label Use
Sheet1

I have a query that brings back data that is separated by a semicolon. (Like the Before sample) I then break those apart into separate columns because each data after each semicolon is it's own code and I am using these in a listbox on a userform.

Manually this is no problem; but I need to validate where these codes are coming from, so I created a query that looks them up and refreshes the list accordingly. Again, this is all manually done right now.

I am looking to automate the steps after breaking the one column into multiple columns (that's easy) Is there a way to then grab all the columns and put them into one ongoing list in one column?
The problem I run in to is that the data could split into Columns B:F or sometimes it goes all the way to B:H so there doesn't seem to be an easy way that I know of to combine all these into one ongoing list.

Criteria that's a must.
1. I have to split them up (semicolon is the factor that helps with this)
2. I have to remove duplicates.
3. Defined Name Range will probably change every once in a while.

 

[Von Pookie]

Nope, sorry. You did get an initial response in the other thread. Please continue there:
http://www.mrexcel.com/board2/viewtopic.php?t=231453