I want to concatenate the data dynamically using Excel VBA Macro and have no idea how to handle the data with empty cells in between and spread over varying number of rows and multiple columns.
Here is the image of sample data and output required..

VBA.jpg










Assuming that

1. Each new row required in the output begins in the sample data with a serial number. However in the sample data, the number of rows for each output data varies, as in the image you see, the first output row is spread over 3 rows in sample data, similarly, the second and third output rows are spread over 5 rows and 4 rows in the sample data.

2. The columns A, B, C, D should have their output in column M
3. The columns E, F should have their output in column N
4. The columns G, H should have their output in column O
4. The columns I, J, K should have their output in column P

Can someone help with this please,

Need a dynamic VBA code which prompts for


  1. Number of output columns
  2. Output column begins with column__
  3. Input column range for each output column.

Thank you!