Duplicate rows

**ducky6** · 08-23-2010, 01:43 PM

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:

mickey mouse	2008	5000
donald duck              2007	7000
donald duck              2008	8500
donald duck              2009	1000
daisy duck	                2006	50000
daisy duck                2007	15000

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:

mickey mouse	2008	5000
mickey mouse	2009	5000 <-- added
mickey mouse	2010	5000 <-- added
donald duck   	2007	7000
donald duck   	2008	8500
donald duck   	2009	1000
donald duck   	2010	1000 <-- added
daisy duck	    	2006	50000
daisy duck    	2007	15000
daisy duck    	2008	15000 <-- added
daisy duck	    	2009	15000 <-- added
daisy duck    	2010	15000 <-- added

Any help is appreciated!
Thank you

**rylo** · 08-23-2010, 07:15 PM

Hi

Assuming your data is in the range A1:C6 try

Sub aaa()
  arr = Array(2010, 2009, 2008, 2007, 2006)
  holder = ""
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Cells(i, 1) <> holder Then
      holder = Cells(i, 1)
      If 2010 - Cells(i, "B") > 0 Then
        For j = 1 To 2010 - Cells(i, "B")
          Cells(i + 1, 1).EntireRow.Insert shift:=xlDown
          Cells(i + 1, 1).Value = holder
          Cells(i + 1, 2).Value = arr(j - 1)
          Cells(i + 1, 3).Value = Cells(i, "C").Value
        Next j
      End If
    End If
  Next i
End Sub

rylo

**ducky6** · 08-24-2010, 03:39 AM

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** · 08-24-2010, 05:44 PM

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** · 08-25-2010, 02:36 AM

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