Macro to copy data from a row (consist of multiple columns) into to single column

Hi,

I am trying to write a macro that select "Sheet 1" and copy certain data from single row (consist of multiple columns) and paste in single column (with stacked rows) in "sheet 2". It definitely sounds like simple transpose but its lot more complicated than simple transpose. I have attached an example excel sheet to my post. In the sheet I just showed a range from A:Y but actual data range is much longer and unknown. Therefore, macro should identify the range and copy into a single column. Any help is greatly appreciated.

Thanks
Aroni


Book1.xlsx

You have merged cells. VBA and Merged cells are not good friends. It is difficult to loop through merged cells.

Unfortunately, I get this sheet from another person. However, I can add a line to unmerge cells in the VBAcode.

I have unmerged the cells and some of the cells end up in different columns.
The code works on the sample.
I have used current region to define my range for efficiency reason. It works fine you can extend your column up to last column ,so long as there is no complete blank row or column.

Thank you very much. You really saved me a lot of time. Now I have a start, I can add additional information to your macro. Thanks again and greatly appreciate your help

Thanks JieJenn.