Macro to Convert Columns to Rows

I have recently inherited a project that has a macro written already. I am very inexperienced with VBA, so I do not know how to modify the macro in a way that will accomplish the new demands of the project.

The macro is designed to take a table of product specifications that are sorted out into columns and transform that data into individual rows for each specification. Currently the macro does just that, but it has some problems / limitations.

I would like the macro to (1) sort the data in column A by product number, (2) keep the existing table of data intact so that if changes need to be made to the table, the macro can be re-run and (3) Copy the table to a new sheet to be used as a backup. Currently the macro works by deleting the whole table and building a new one.

I have attached a sample of what I start with, what I get, and what I would like to end with. Keep in mind that the amount of data being worked on in a real scenario will have dozens of columns that will change with each new task (Meaning I need the macro to be flexible in counting the number of columns that are populated rather than just worked with a pre-defined range).

This is the relevant part of macro that's doing the work:

Please Login or Register  to view this content.

Thanks for any and all help.

I have not looked at your code, but looked at your desired result sheet and came up with the attached code.

That works, but it has one major limitation. Some of the specifications are blank for some products, but they get copied over regardless of whether a product has that specification or not. The value of the new specifications is blank, which is correct, but I'd like to avoid having that row generated at all.

My code was based on the data you provided- from The start sheet to where to. You clealry labled the cells in what you wanted, but no harm is done as you can use your existing code.

You're correct - your method works great with the data I provided. I didn't mean to insult you, I just provided an incomplete / bad example of my actual needs.

Your macro works more quickly and can do new things compared to the old macro I inherited, so I'd like to use it as long as it can be adapted to my needs. I have attached a new file with some blank specification values and comments to demonstrate the results I'm looking for. Any help would be greatly appreciated.

Hi -

Another approach that you can consider.

Please Login or Register  to view this content.

Regards,
Event

Hi,

I have a similar problem but not as complex. I have an "email list" that I pulled from the TO column in outlook. I pasted that list into a text file and saved it as text file with Unicode. All the email addresses (with names) are separated by a ";". when I imported into excel and used the ";" to import the data with that as the delimiter, it puts all the names and emails in columns in row 1. I want to have 1 name and email per line. I am trying to make an attendees list. I can handle manipulating the data once there is one name per line. I would think this would be a much simpler macro than above but similar. Any ideas:

HR

HR,
Please start your own code. It against the rule to reply to this post.
Please attach a sample book with your desired result. I will keep an eye on your post and hopefully should be able to help.