Restructuring data

Hi all,

I’m trying to write some VBA which can reorganise my data but I literally have no idea where to start with this one. I’ve attached an example workbook of a before and after showing how my data is and how I need to transpose it.

Can anyone provide any pointers? I was thinking I need a loop maybe?

I suspect your real data doenot look like the sample attached. You need to attach a realistic sample if the code does not produce the desired result.

HI AB,

No my code does look like the attached, certainly in structure anyway. Only difference is content. Any ideas where to start?

There is a code attached to a button. When you press it, the output would be in new sheet. What I was trying to explain is if the code does not produce the desired result, you need to increase the sample data and layout. The code produces the correct result, but it is only based on two rows of data.

That is excellent. Thank you for supplying the answer. I am fairly ok at reading VB and writing rudimentary code but I have no idea what yours is doing!

I suspect I need to add:

Please Login or Register  to view this content.

somewhere but I'm not sure where?

It finds the last row in column A. It returns a long(Long integer). The last row with data before a blank row.

This is not part of my code though.

Thanks AB. When I run your code I get a subscript out of range error which I suspect is because my row range is larger than my example sheet. It's currently 158 rows (always 5 columns though). Sometimes though it might be more rows or less.

Because I don't understand your code I'm not sure where I need to put in the finalrow code?

As long as you have data starting in row 2 and there is no complete blank rows or columns, the code could handle any size.
Could you please attach your sample?

Hi AB,

I see, thanks. I have attached a real sample of my data. It actually goes up to 18 columns. Is that why it doesn't work?

Thank you for your help.

D_N_L,
I did not get any error when I run the code. You have not included an output sheet. Please check the output on sheet5. If the output is different, please attach a new output.