Advanced transpose macro (VBA help)

[Nils88]

Hi,
A couple of weeks ago I got help from a very kind person called "yudlugar" in this forum but not\w I need to modify his solution a bit. I cant seem to reopen the last thread so that is why I made a new one. Here is a link to the old one:http://www.excelforum.com/excel-prog...-vba-help.html

Please Login or Register  to view this content.

What has happened is that I am adding rows to the data each month (right now up to ROW AE) and therefore the macro does not work any more. I have updated the code manually by changing the values highlighted in red above, but that is not very efficient, and also very complicated to explain to someone else how to do....

Therefore, I was hoping that someone could show me how this can be done automatically. For example by counting how many of the cells in row 1 contains numbers, i guess it would be easy to do this, but I only know how to count in excel hehe and not VBA.

For example now it is from A to AE = 31 rows... And from F (6) to AE (31) = 26

Thanks a lot in advance!

Kind Regards

[alansidman]

Add this to your code

Please Login or Register  to view this content.

Change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I can't help with the 25 because I don't understand what that is doing or what you want there.

[Nils88]

Dear alan,
Thanks a lot for your reply.

I replaced what you suggested so it now looks like this.

Please Login or Register  to view this content.

Unfortunately however it does not work.

Firstly when I change

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

i get a message saying
"Run-time error '1004':
Application-defined or object-defined error"

I know the lc works however and that it is 31 now...

Secondly, I cant really explain why, but I need to be able to change the parts highlighted in blue and purple

The blue one should always be lc - 6, which in the current situation is 25, and the purple one should always be lc - 5 (26 now)...

Is this possible?

Thanks in advance!

Regards

[nilem]

Hi Nils,
try it

Please Login or Register  to view this content.

[nilem]

... or this option

Please Login or Register  to view this content.

[Nils88]

nilem,
Thanks a lot for your help.
Both of them works perfectly.

The only difference I can see is that
- "Macro_1" takes around 3 times as long as the "ertert" macro to perform the macro.
- in order for "ertert" to work, I need to have a sheet in the workbook called "sheet2"
- "ertert" turns all formating to "general" and therefore percentages etc. is converted to general...

Why is ertert so much faster, and would it be possible to make it create a new sheet automatically instead of using sheet2?

Once again thanks a lot, you have been truly great!

[nilem]

Nils,
"ertert" takes only values​​, so we look at the data in a format General on sheet2. Just set the desired format for certain columns on sheet2 and then run the macro.
"ertert" uses arrays, and it is much faster than copy-paste

[Nils88]

Thanks a lot!

[Nils88]

Hi Nilem,

Actually I just found out that it does not work when I include all products. As it says in the excel doc that you also been working with, I actually have 10000+ rows of data, with more than 1000 products. For some reason not all these products are included in the macro output (only around 100 actually). can this be because some of these products are missing a lot of data, and that the code then does recognize that they are there and should be included? Column A to E are always filled 100% with data until the last product. But Columns F and above can be 100% empty or at least some cells can be empty for the products... I do not understand how this array using code works, so I cant modify it. could you please take a look at it again? I truly appreciate it!

Regards

[Nils88]

Hmm I think there is something wrong with my data, as I have found that sometimes there are 2 different product codes in the 11 rows of data for each product. This seems to be the problem with the macro, and by fixing the data the code will work. Thanks anyways!