Convert many non-blank columns to rows

**smytgemm** · 03-25-2013, 09:14 AM

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

**arlu1201** · 03-25-2013, 09:22 AM

Do you have a sample file that you can upload? It will be easy to understand than reading the data from the post.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.