Hi all,

I'm new to both VBA and the forum. I've come across a problem that I can't seem to find a solution to and I'm hoping you can help me out.

My worksheet consists of 4 columns of data. A "Action", B "Start Date", C "End Date" and D "Duration".
In column D I want to calculate the duration in days between the two dates. Which is very easy in the sheet itself. Now I recorded a macro which calculates the duration and that also autofills the values in the rows below the first calculation.

The below code is what the Excel recorded. Now this works just fine if I stick with data in that range, however if new rows are added to the columns A-C the calculation would then still only be run until cell D5 when the macro is run once more. How would I go about solving this, making it "dynamic" in relation to the data columns to the left of column D?

Sub CalculateDuration()

' CalculateDuration Macro

    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
        Selection.AutoFill Destination:=Range("D2:D5")

End Sub
I would very much appreciate your input! Thanks in advance!