Combining multiple columns into one column

**Casper_DK** · 06-11-2011, 02:35 AM

Hi

I a total rookie into VBA-programming and hope someone can help me.

In my sheet I have multiple columns that I need to combine into one column:
- In this example (see attachment) I have 4 columns E:H I need to combine into column C.
- There will be data in the columns nearby the range I like to combine.
- I need to do this on different data sets where the number of columns (and rows) will vary, therefore it could be nice to have a field where I can give input on how many columns that should be combined - in this example cell E2.
- I don't want to have a function that can concatenate the columns.

I have not succeded to find a code that I can adapt for that purpose. I will be happy if there is someone who can help me.

Thanks in anvanced!

Br.
Casper The friendly VBA rookie ;-)

**Marcol** · 06-11-2011, 05:39 AM

You could use a UDF to concatenate the columns, then Copy > Paste Special > values

In a standard module

Please Login or Register  to view this content.

Then in C5

Please Login or Register  to view this content.

Drag/Fill Down (or, in this example, double click the fill handle)

If you really need VBa to do this for you, then we could add 3 named ranges
"Start_Column"
"Start_Row"
"NumberOfColumns"
and put the starting parameters in these cells
(See the Names Manager).
By using Names the VBa will not be affected if you decide to rearrange these cells, just drag them to somewhere that suits.

Add this routine to the module

Please Login or Register  to view this content.

Select whatever cell you want the combined column to begin in then run the macro "ConcatenateColumnsByRow", this cell can be anywhere on the sheet, not necessarily C5

Hope this helps