Insert Number of Rows Based On Number of Populated Cells and Transpose Data

Hi everyone,

I have a sheet containing a date in column A, a description in column B, and a varying number of reference codes in columns D to AZ.

What I'm trying to achieve is to count the number of codes in (for example) cells D2 to AZ2 (ignoring empty cells), insert the equivalent number of entire rows into the table immediately following the row containing the data, copy the codes (again ignoring the blanks) and transpose the data, pasting it into column C. After doing this, I want to copy a single cell (O1 in the attached example) and paste it into column D an equivalent number of times to the number of codes which were just pasted.

Then repeat down the sheet.

Hopefully the attached example helps.

Thanks in advance to anyone who can help me with this....

Andy

You could do it with a few formulae, but as you have posted in the programming forum presumably you are looking for a macro to do it for you, so I'll leave that for someone else.

Pete

Hi Pete,

If you have any tips on doing this with formulae, I'd be happy to hear them!

Andy

Hi Alan,

I need to end up with all of the reference codes in one column.

Andy

VBA

Please Login or Register  to view this content.

Thank you +jindon, that works great!!

I have some questions though:

1. I want to move the word 'Apples' into cell C2 on a separate sheet called 'XDATA' within the same workbook. How can I do this?

2. When I am using the VBA with different workbooks in the future, the number of columns containing the codes will vary. Can I specify a number in cell 'XDATA' cell D2 which will allow me to change the total number of columns containing the codes?

3. When I am using the VBA with different workbooks, the number of columns at the beginning of the sheet containing data such as 'Date', 'Description', etc, will vary. Can I specify a value for this in 'XDATA' cell E2?


Andy

Not clear to me,

If you upload a workbook the the data and all the conditions in 'XDATA', it will help.

I have attached an example, plus I have added more features in XDATA to improve the flexibility of the project.

OK, thanks for the clear explanation.

Delete row(1) from "Source Data 123" sheet and run the code.

Please Login or Register  to view this content.

Thanks, that works great in your example.

However, once I had added my own data and increased the number of columns, i get a 'Subscript out of range' error at the following line:

b(n, ii) = a(i, ii)

Also, I have added some more fields to the XDATA sheet. Sorry to keep making changes.

Please see the attached example.

Andy

Are you sure that you always have blank(s) in the header row?

Yes, there are often some entirely empty columns in our sheets - but if that is a problem we could add some data into the header row here ("EMPTY", for example).

The result is obviously different from yours...

Please Login or Register  to view this content.

Thank you, that's absolutely perfect. I really appreciate your help......