Duplicate rows

[ducky6]

Hi, everybody
I hope some of you could help me with this problem. I'm not a macro expert and I couldn't find any solution on the web yet.

I have an Excel file that looks like this:

Please Login or Register  to view this content.

and I need a macro which creates duplicate rows for every person, filling the missing years until the current year.
So the result for this example should be:

Please Login or Register  to view this content.

Any help is appreciated!
Thank you

[rylo]

Hi

Assuming your data is in the range A1:C6 try

Please Login or Register  to view this content.

rylo

[ducky6]

Hi, Rylo!
Thanks for your prompt reply.

I tried your code, and it seems you are on the right path!

I just have one problem: in my original file I can't know in advance which is the beginning year for every person: it could be 2007, 2002, 1994... anything.
Do I need to specify a huge range of years in the array variable (e.g. starting from 1900), or is there any smarter way?

Thank you so much for your assistance.

[rylo]

Hi

From your example, you don't need to know the starting year for each person, but the last year, as you only seem to be going from the last year to the predetermined "current" year.

1) How do you want to determine the "current" year? Have it taken from the current date? Or have an input box / variable that you can select it?

2) Once you have that determined, it would be easy to get the min value from the year column. You could them build the array based on a descending loop from the Max year, to the determined Min year.

Make sense?

rylo

[ducky6]

You are right, I can populate the array with a loop. Maybe my question was a little bit silly.

I set this topic as "resolved" and I hope it will be useful for other people, too.

Thank you very much for your kindness,
hope to see you again on this forum