Macro to Autofill

Question

I want to create a column in P called Age of Ticket. It is calculating two dates and then autofill. This macro will be used for reports that vary the number of rows. When I use this, it basically does the calculation on P2, then it does the calculation on P1 (where the label is) and doesn't autofill.

Dim LastRow As Long

LastRow = Range("P65000").End(xlUp).Row

Range("P1").Select
ActiveCell.FormulaR1C1 = "Age of Ticket"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]-RC[-8]"
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P" & LastRow)
Range("P2:P3000").Select
ActiveWindow.SmallScroll Down:=210

[Results]

score 5 · Accepted Answer · edited May 23 '17 at 12:31

The issue is that the column when it is added is empty. So your code is telling excel to go to P65000 and mimic hitting ctrl-upArrow. This would find the first cell with a value, but since there are no values it stops at the top cell P1, thus LastRow = 1. So the fill area is P2:P1. Use a column that has data in it. I used the K column because it is in your formula.

This is great example of the use of the Record macro and attempt at cleaning. When cleaning the code one focus should be to get rid of the .Select. See here for a great compilation of methods to reference ranges without the .Select

Also when using the .FormulaR1C1 the entire range can be set at once and does not need the fill down.

Change the above code to:

Dim LastRow As Long

 LastRow = Range("K65000").End(xlUp).Row

Range("P1").value = "Age of Ticket"
Range("P2:P" & LastRow).FormulaR1C1 = "=RC[-5]-RC[-8]"

Also if you wanted to use the Autofill, there is no need to use the r1c1 formula.

Range("P2").Formula = "=K2-H2"
Range("P2").AutoFill Destination:=Range("P2:P" & LastRow)

This would automatically change the relative references to keep the rows correct.

Macro to Autofill

1 Answers1