Hi,

I am trying to use VBA to convert a spreadsheet to a different layout,

The data is as follows

Name Currency total Australia Japan China New Zealand Algeria Bahrain
John aud 500 1,006.00
Andrew aud 1000000 2,139.00 36,000.00

and I would like this data to appear as follows (without the blanks!!!)

Name Currency Total Country Country Total
John AUD 500.00 Australia
John AUD 500.00 Japan 1,006.00
John AUD 500.00 China
John AUD 500.00 New Zealand
John AUD 500.00 Algeria
John AUD 500.00 Bahrain
Andrew AUD 1,000,000.00 Australia 2,139.00
Andrew AUD 1,000,000.00 Japan
Andrew AUD 1,000,000.00 China
Andrew AUD 1,000,000.00 New Zealand 36,000.00
Andrew AUD 1,000,000.00 Algeria
Andrew AUD 1,000,000.00 Bahrain


Final Version
Name Currency Total Country Country Total
John AUD 500.00 Japan 1,006.00
Andrew AUD 1,000,000.00 Australia 2,139.00
Andrew AUD 1,000,000.00 New Zealand 36,000.00




The data set I am using is very large, on the original it has 300+ rows and 100+ columns so when this is transposed it will be even larger,


So I am basically trying to transpose the non blank columns to rows, I have tried the following but it is very slow and includes all rows without anyway around this,
http://www.mrexcel.com/forum/excel-q...ml#post2323445


Is there anyway to do this without using arrays as I'm not too comfortable with VBA yet?

I will be running this process on a regular basis and the data will be dynamic so may increase in number of rows, this will all be in Excel 2010.


Thanks

Gemma