Macro to Find end of Column and paste Contents in Column 1

I have the file here i work with, basically the first column is a legend and the column to the right of it is a pointer column to help me find out where a legend is located in another file. So i was wondering if a macro could be made to basically find where the "legend column" A, C, E ect ends ( every other column is a legend column , one next to it is a pointer column ).

and then combine the ends all of the columns contents and put them into 1 column.

In the file with this question i have showed you what i start off with, i highlighed in yellow where each column legend ends, ( normally these are not highlighted and i find them manually ).

In the 2nd tab i show what the end result should be. All the columns are now consolidated into 1 column. 1 after the other.

any help would be great, thanks

Put this macro in a standard module and run it on a copy of your data. Should do the trick:

Please Login or Register  to view this content.

OK works perfectly, is there anyway you can have it work to where the tabs dont have to be named that.

For instance it should put the info in the tab to the right of the tab i run the macro in, that way i dont have to have the tabs named anything in particular.

You can change the names of the sheets in the macro to anything you want. Just spot the "Start off with" and "Ends up with" and insert your other sheet names instead.