VBA code to concatenate and preserve formatting

Hello all,

I have a question with regards to VBA. I'm trying to concatenate multiple columns of data into one column whilst preserving the custom number formatting, that is to say column A's values are all on 3 positions, column B's values are all on 4 positions and column D's values are all on 20 positions.

In the example attached I have very little data but this VBA needs to generate more than 200,00 rows of concatenated data in one go.

The excel file attached already has some VBA code but it does not preserve the formatting of the source columns in question.

Any help would be greatly appreciated.

Thanks,

Sleinous

Test_cid.xlsm

Thanks for the fast reply! Do you mean in the source columns? A to D? If so, unfortunately I can not format these as text as these 4 columns are in turn the result of another macro that dumps the data into them - the fat that they are custom formatted forces excel to display the data with 0's infront which is vital for the software used.

Ah! Got it thanks!