Filling in dates (and creating rows)

[RhysPhelps]

Bonjour everyone. Thanks to Yard's extermely useful help i am nearly the envy of the office but i now have another problem.

I have a spreadsheet of contracts that are about to expire and am creating a pivot table from it so that i can add the details to a calendar template (Don't ask :P). however a contract doesn't expire on everyday of the year so i need a macro that can create rows and insert the next date.

For example.

The end date is contained in column F so at the moment i have

F
1 End Date
2 01/01/2009
3 04/01/2009
4 05/01/2009
5 07/01/2009
and so on

What i would like the macro to do is insert 2 rows between F2 and F3 and fill in the missing dates. There are other columns with data but they can be filled in using cut and paste

The Sheet is called contracts and the End date column goes to F250 (although the sheet may change month by month so if it is possible to just perform the action on F2 onwards that would be brilliant)

I'm a complete newbie on VB and macros but i have been having a read so if anyone has some recommended texts or websites then hit me up

Ta all

 

[Weaver]

try this on a copy of your data

Sub fillDates()
    Application.ScreenUpdating = False
    For x = Cells(Rows.Count, "F").End(xlUp).Row To 2 Step -1
        If x = 2 Then
            g = Cells(x, "F") - DateSerial(2008, 12, 31)
        Else
            g = Cells(x, "F") - Cells(x - 1, "F")
        End If
        If g > 1 Then
            Cells(x, "F").Resize(g - 1, 1).EntireRow.Insert Shift:=xlDown
            If x = 2 Then
                Cells(x, "F") = "1/1/2009"
                Cells(x, "F").Resize(g, 1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Trend:=True
            Else
                Cells(x - 1, "F").Resize(g + 1, 1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Trend:=True
            End If
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

Note this code (hopefully) allows for the fact that there might not be an end date on 01/01/2009

 

[Weaver]

PS if you let me know which columns are to be copied/pasted into the new rows, it should be possible to incorporate this into the macro.

 

[RhysPhelps]

That is outstanding. Works a treat. Thank you sooooo much

The only other column is "M" which is the contract value. this would obvioulsy be filled in with "€0.00" but thats easy enough to just filter the blank cells and copy and paste

lots of love

Rhys