Change a table (col to rows and vice versa)

[masben]

Hi,

I have a table showing products per location. I have locations in col A and products in col B.

I would like a macro to change the format of a table in the way that columns becomes rows and rows becomes columns - in a way...
The location should be stated in Row A and the products in the rows below (same column).

Please look at the attached example.

The tabel change every month - new locations are added as well as new products are added to existing locations.

Thanks for your help!
//Martin

[DavidJAllen]

Martin,

The attached has a macro that runs when you press the button.

The code is below. It will automatically select all relevant data in columns A and B so no matter how many rows you have it should work. The top left hand output cell is set in the code which you can change.

Please Login or Register  to view this content.

Any questions let me know.

Regards

David

[masben]

David! Many thanks!

Your macro does exactley what I wanted :-)

With one exception... (but I forgott to mention it in my problem description so - my fault..)
If there has been no sell of any product at a certain depot (i.e. all cells in the B column is blank for that location) the macro stops at the previous location but I would like the 'empty' location as well as the following locations to be included. Se the attchment.
Do you think it's possible?

Thanks again,
Martin

[DavidJAllen]

Martin,

In this version of the worksheet you need to select the table range before pressing the button, it works as you want, all I did was comment out the 3 lines of code that selected the data range.

Excel needs to know where you data is, in the first version it did this automatically assuming that all cells in column B were filled in. In this one you select the range before you press the button to run the macro. The third possibility would be for the user to put the range in a cell or named range that would need to be updated.

Regards

David

[masben]

Thanks again David!!

This version suites all my needs. Awsome!

Reputation added with pleseure :-)

All the best,
Martin