# Fill down formula on relative range

#### Serafin54

##### New Member
Hello.

I have a vba that I recorded. The column can change from report to report but I need the formula to fill down to the last row of data. The report could be 10 rows or 10000 so what i currently have isn't working very efficiently.

Any explanation would also be greatly appreciated.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Joe4

Are you inserted a blank column A and then inserting this formula?
You have posted an image, and we cannot copy/paste that to the VB Editor to save us from rewriting all your code manually.

Just copy/paste the text of the code here, then apply the Code Tags as shown here:

#### mrshl9898

##### Well-known Member
Try:

VBA Code:
``````Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column))

End Sub``````

#### Joe4

If my original assumptions are correct, and the first formula goes in cell A2, you can simplify mrsgl9898's code a little like this:
VBA Code:
``````Sub Time_Period_From_Date()

Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Columns("A:A").Insert shift:=xlToRight
Range("A1") = "Time Period"
Range("A2:A" & LastRow).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"

End Sub``````
Note: Generally, I do not like using "ActiveCell" in VBA code, as that depends on the person being in the correct cell at the start, or else it won't work correctly.
If you want to start in a definite place, use that cell reference in your code.

#### Serafin54

##### New Member

Try:

VBA Code:
``````Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column))

End Sub``````
Are you inserted a blank column A and then inserting this formula?
You have posted an image, and we cannot copy/paste that to the VB Editor to save us from rewriting all your code manually.

Just copy/paste the text of the code here, then apply the Code Tags as shown here:
My apologies.

VBA Code:
``````Sub Time_Period_from_Date()
'Will get the formatted time period from the date column
Selection.Insert Shift:=xlToRight
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Time Period"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=""Check Date - Qtr ""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8999")
ActiveCell.Range("A1:A8999").Select
End Sub``````

To answer your questions I'm not inserting a column. I am highlighting the column with the date and then running the vba. The column "floats" so it may never be in the same place depending on the report but the check date quarter is always adjacent to the left.

#### Serafin54

##### New Member
Try:

VBA Code:
``````Sub FindingLastRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column))

End Sub``````

This works as needed. Thank you

No worries

#### Serafin54

##### New Member
If my original assumptions are correct, and the first formula goes in cell A2, you can simplify mrsgl9898's code a little like this:
VBA Code:
``````Sub Time_Period_From_Date()

Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Columns("A:A").Insert shift:=xlToRight
Range("A1") = "Time Period"
Range("A2:A" & LastRow).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"

End Sub``````
Note: Generally, I do not like using "ActiveCell" in VBA code, as that depends on the person being in the correct cell at the start, or else it won't work correctly.
If you want to start in a definite place, use that cell reference in your code.
Ok this is much neater than my recorded macro. I'm going to need to dissect this so i can understand what is going on so i can shorten my others. when you say it depends on being in the correct cell, what do you mean?
Thank you very much btw.

#### mrshl9898

##### Well-known Member

VBA Code:
``````Sub FindingLastRow()

Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Selection.Insert shift:=xlToRight
ActiveCell = "Time Period"
Range(ActiveCell.Offset(1, 0), Cells(LastRow, ActiveCell.Column)).FormulaR1C1 = "=""Check Date Qtr""&ROUNDUP(MONTH(RC[1])/3,0)"

End Sub``````

Replies
18
Views
589
Replies
3
Views
193
Replies
8
Views
144
Replies
2
Views
130
Replies
1
Views
170

1,136,855
Messages
5,678,143
Members
419,746
Latest member
tysonboy82

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back