**Tom’s Tutorials For Excel: Fill Formula to Last Used Row**

The picture on the left shows a list of numbers in column A starting in cell A2. You have a formula in cell B2 which you want to quickly copy down to the last used cell in column A.

With cell B2 selected, position your mouse directly over the fill handle (that tiny black square in the lower right corner of the active cell) until the mouse cursor looks like a crosshairs, as pointed at by the red arrow. Double-click the left mouse button to fill the formula down as shown.

In a macro, you can declares a variable for the last used row in column A and fill the formula in cell B2 down to the corresponding cell in column B.

The example formula as shown in the address bar in the picture is `=$A2+1`

The VBA equivalent of that formula is `=RC1+1`

so a macro could be:

Sub FormulaFill() Dim LastRowColumnA As Long LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row Range("B2:B" & LastRowColumnA).Formula = "=RC1+1" End Sub

Hi,

i tried u r macro as it is

Sub FormulaFill()

Dim LastRowColmA As Long

LastRowColmA = Cells(Rows.Count, 1).End(xlUp).Row

Range(“B2:B” & LastRowColmA).Formula = “=RC1+1”

End Sub

But result is

Number Number +1

79 1

46 1

94 1

52 1

68 1

97 1

24 1

53 1

51 1

58 1

74 1

Pls correct me, where i did wrong

What is in cell A1? The formula works, so does the macro, so I wonder if you are referring to cells that actually have numbers in them, as opposed to cells with nothing in them, or text in them.

Try replacing this line

Range(“B2:B” & LastRowColmA).Formula = “=RC1+1”

with this instead

Range(“B2:B” & LastRowColmA).Formula = “=RC[-1]+1”

Now its working fine

thank you

Hi,

I am also working same kind of file but my requirement is. I have to fill date for the Last row in Y25″.

But I have some other data in “A” column as well

X Y

5 Customer/BDM In-charge

6 TOTAL

7 SDL

8 Mascot

9 ANDREAS SINGER

10 BRYCE DUKES

11 DEEPAK BANSAL

12 DE-LESCAZES CORALIE

13 Farhan Cutlerywala

14 FONTAINE MA

15 GUS GARCIA

16 Iris Teu

17 June Hung

18 MATTHEW RIGBY

19 NICKY ONG

20 NIRMALA MAGECHA

21 NITIN SHENDYE

22 Partha Guha

23 RK CHAUDHARI

24 ROSALYN FRAYNA

25 ROSHAN VYAS

26 SINGH, GUNTESHWAR

27 VIC MEMITA

28 VIVEK MUNGE

29 WUNNIK, DANIEL-VAN

1

2

3

4

Grand Total 5

7,796 6

2,680 7

7,555 8

12,626 9

44,699 10

12,224 11

14,226 12

20,905 13

122,710 14

50,055 15

(261) 16

192,011 17

29,575 18

16,415 19

(53,965) 20

115,759 21

349,588 22

237,566 23

(62) 24

50,846 25

84,564 26

(2,139) 27

485,477 28

1,370 29

857,622 30

I do not understand your question. The last row in Y25 would be row 25 if that is only how far down you want to fill dates.

Hi Tom

I have a similar script

Sub FillFormula()

Range(“Q2”).Formula = “=SUM(H2+I2)”

Range(“Q2”, “Q” & Cells(Rows.Count, “A”).End(xlUp).Row).FillDown

End Sub

I am trying to perform a simple addition and place the result in Column Q. If I have more than 1 line of data the formula works. But, if i only have one line the macro doesnt seem to place the result of the formula in cell Q2. i think the result gets deleted due to fill down. Also for example, if I didn’t have any data, then it copies the headings in Q1 and repeats that in Q2. Are you able to explain how i could possibly fix the macro?

Regards

Roopak

Hello Roopak – –

This will cover all your possibilities, including if there is no value in cell A2, or just cell A2 has a value, or many cells in column A have values in them

`Sub FillFormula()`

Dim LastRow As Long

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

If LastRow > 1 Then Range("Q2:Q" & LastRow).FormulaR1C1 = "=RC[-9]+RC[-8]"

End Sub

Hi, this is great. thank you.

I have another file where I need to autofill. Columns A:E, There are values in multiple rows and there are blank rows in between these values. I like to autofill every blank cell (A:E) with the value above.

Ex:

A b c d e

1 2 3 4 5 (fill this down to next)

6 7 8 9 4 (fill this down to nex)

3 5 6 7 4

You can do it manually as I sjhow here in this cideo…

https://www.atlaspm.com/toms-tutorials-for-excel/toms-tips-for-excel-videos-filling-empty-cells-with-value-from-above/

or you can do it with a macro like this:

`Sub FillCells()`

Dim NR As Long, i As Integer

NR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

With Application

.ScreenUpdating = False

For i = 1 To 5

On Error Resume Next

With Range(Cells(1, i), Cells(NR, i))

.SpecialCells(4).Formula = "=R[-1]C"

.Value = .Value

End With

Err.Clear

Next i

.CutCopyMode = False

.ScreenUpdating = True

End With

End Sub

Hi Tom

Thanks heaps for your help. In my comment earlier i had used an example. But the actual code i use is as follows. Is there a way i could use something similar in the FormulaR1C1 space? As in can i just copy my If statement as per below script and paste it? Would it perform the calculations correctly?

Range(“R2”).Formula = “=IFERROR(ROUND(IF(N2=””EQUITY””,MIN(G2,MAX(40,J2*0.6%)),MIN(G2,MAX(30,J2*0.4%))),2),0)”

Range(“R2”, “R” & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Range(“S2”).Formula = “=IFERROR(ROUND(IF((G2-R2)<0,0,(G2-R2)),2),0)/IFERROR(VLOOKUP($K2,'Daily FX Rate'!$A:$P,HLOOKUP($F2,'Daily FX Rate'!$B:$P,2,FALSE),FALSE),"""")"

Range("S2", "S" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Range("T2").Formula = "=IFERROR((G2/J2),"""")"

Range("T2", "T" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Range("U2").Formula = "=IFERROR(MONTH(K2),"""")"

Range("U2", "U" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Your original question’s example is quite a bit different than your actual code example, but the concepts are the same as to what I posted to your original question, so just modify your codes accordingly. You can answer your own question about if it would perform the calculation correctly if you go ahead and test it to see for yourself.

Hi Tom

I am trying to filter zeros out of a data range (which is not a table) using a macro. But unfortunately something is wrong with the last line of the macro. Are you able to please let me know what is wrong with my code here?

Sub Macro3()

Dim Source As Variant

Dim LastRow As Long

Dim criteria As String

Source = ActiveWorkbook.Name

Sheets(“Parameters”).Select

ActiveSheet.Range(“I3:J”).AutoFilter Field:=2, Criteria1:=”0″, Operator:=xlFilterValues

End Sub

My first suspicion would be to edit the last line of the macro for a specific range including the last row. You have I3:J but no last row number for column J.

Gotcha.. It worked now.. I amended the formula .. Thanks again

Thanks for the update.

Sorry another troubling question.. How do you write this expression in VBA

=”Test”&” “&PSheet1!$A$3&” “&sheet1!$B$3

I am trying to write this expression in the vba code below

Range(“I2:I” & Lastrow).Formula = “=”Test”&” “&PSheet1!$A$3&” “&sheet1!$B$3”

Try using the macro recorder to see the code that would be created.

Hi

I am looking for a VBA to open a workbook (FX rate log) from a specific file path and the look for a specific date (Today()-1) and copy the rows including the date and various currency rates to another workbook (Working model) within worksheet (FX Rate).

Data layout in FX Rate log – (Worksheet “Sheet 1)

Date AUD CAD USD ZAR

05/06/2019 0.9456 0.7665 0.7789 5.4321

06/06/2019 0.9457 0.7890 0.7898 5.6543

07/06/2019 0.9467 0.7564 0.6987 5.3215

Working Model – (Worksheet “FX Rate”)

Date AUD CAD USD ZAR

05/06/2019 0.9456 0.7665 0.7789 5.4321

06/06/2019 0.9457 0.7890 0.7898 5.6543

I would like to get the entire row of 07/06/2019 from FX rate log without the headings under the working sheet . In the working model workbook I have the date 07/06/2019 in cell “B2” of sheet 1 which I change daily for reporting. So if the macro could use that date as a reference it would be even better so I can ignore Today()-1

Thanks

Roopak

I will be unavailable for the next week on a client project. This would be a good question to ask on a popular forum that I am also registered to, with many experts readily available to help you, at https://www.mrexcel.com/forum/excel-questions/.

Hi Tom!

I have a range of 8 digits numbers in the “A” column and I need to fill the “B” column with the formula that takes the first 4 digits of those numbers. I tried to apply your code for this case:

Sub FormulaFill()

Dim LastRowColumnA As Long

LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row

Range(“B2:B” & LastRowColumnA).Formula = “=Left(A2;4)”

End Sub

Still it doesnt work and I get an error. Could you please have a look at my problem?

Thank you for your help in advance,

Max

Hello Max – –

The code you posted works for me, I just tested it no problem, EXCEPT that (and maybe this is a regional syntax difference depending on where you are located geographically) I had to substitute your semicolon with a comma, to make this…

`"=Left(A2;4)"`

…look like this

`"=Left(A2,4)"`

Hi Tom, now it finally works! Thank you a lot!

Great, thanks for the update!

Hi Tom,

I’m creating a macro to help with the batch renaming of files. I have 4 formulas I need to autofill down to the end of the data in the adjacent column (D), populating E, and so forth. Because there won’t always be the same amount of files to rename, I’m not sure how to create the macro so that it ends at the end of my data. Right now this below works because I placed the end row of “E9999” but this is obviously not ideal, and I have to delete a lot of extraneous data. I apologize for the beginner errors, I’m very new and self-taught. Greatly appreciate ANY help whatsoever!

Some formatting takes place before this, but here’s my problem area:

Range(“E1”).Select

ActiveCell.FormulaR1C1 = “=CONCATENATE(“”.””,RC[-1])”

Range(“E1”).Select

Selection.AutoFill Destination:=Range(“E1:E9999”)

Range(“E1:E9999”).Select

Range(“F1”).Select

ActiveCell.FormulaR1C1 = “=CONCATENATE(RC[-5],RC[-1])”

Range(“F1”).Select

Selection.AutoFill Destination:=Range(“F1:F9999”)

Range(“F1:F9999”).Select

Range(“G1”).Select

ActiveCell.FormulaR1C1 = “=CONCATENATE(“”””””””,RC[-1],””””””””)”

Range(“G1”).Select

Selection.AutoFill Destination:=Range(“G1:G9999”)

Range(“G1:G9999”).Select

Range(“H1”).Select

ActiveCell.FormulaR1C1 = “=CONCATENATE(“”ren “”,RC[-6], “” “”,RC[-1])”

Range(“H1”).Select

Selection.AutoFill Destination:=Range(“H1:H9999”)

Range(“H1:H9999”).Select

Range(“A1”).Select

End Sub

Hello Taya – –

I was out of town, traveling on business and just saw this. Is this still an open item or have you found a solution?

Still need a solution if possible, thank you!!

Try this

`Dim myLastRow&`

myLastRow = Cells(Rows.Count, 4).End(xlUp).Row

Application.ScreenUpdating = False

Range("E1:E" & myLastRow).FormulaR1C1 = "=CONCATENATE(""."",RC[-1])"

Range("F1:F" & myLastRow).FormulaR1C1 = "=CONCATENATE(RC[-5],RC[-1])"

Range("G1:G" & myLastRow).FormulaR1C1 = "=CONCATENATE("""""""",RC[-1],"""""""")"

Range("H1:H" & myLastRow).FormulaR1C1 = "=CONCATENATE(""ren "",RC[-6], "" "",RC[-1])"

Application.ScreenUpdating = True

Good Afternoon,

In my macro I am simply trying to have the formula that is in A2 fill all the way down column A to where the last data row of column B. The rows in column B will be variable. This range is currently “A2:A5000” but I would like it to be “A2:A(last data row of B).” Can you take a look and let me know how I can achieve this?

Sub MontlyStatements()

‘

‘ MontlyStatements Macro

‘ Delete Row 1; Delete Column K; Insert new Column A and titel it “Balance Due;” Enter formula in A2 and drag to final cell; Copy Column A and paste as Values

‘

‘ Keyboard Shortcut: Ctrl+i

‘

Rows(“1:1”).Select

Selection.Delete Shift:=xlUp

Columns(“K:K”).Select

Selection.Delete Shift:=xlToLeft

Columns(“A:A”).Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range(“A1”).Select

ActiveCell.FormulaR1C1 = “Balance Due”

Range(“A2”).Select

ActiveCell.FormulaR1C1 = _

“=SUMIF(C[7],RC[7],C[12])+SUMIF(C[7],RC[7],C[13])” & Chr(10) & “+SUMIF(C[7],RC[7],C[14])+SUMIF(C[7],RC[7],C[15])” & Chr(10) & “+SUMIF(C[7],RC[7],C[16])+SUMIF(C[7],RC[7],C[17])”

Range(“A2”).Select

Selection.AutoFill Destination:=Range(“A2:A5000”)

Range(“A2:A5000”).Select

Columns(“A:A”).Select

Selection.Copy

Range(“A1”).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

Try this:

`Sub Test1()`

Application.ScreenUpdating = False

Rows(1).Delete

Columns(11).Delete

Columns(1).Insert

Range("A1").Value = "Balance Due"

Dim LastRow As Long

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

With Range("A2:A" & LastRow)

.FormulaR1C1 = _

"=SUMIF(C[7],RC[7],C[12])+SUMIF(C[7],RC[7],C[13])" & Chr(10) & "+SUMIF(C[7],RC[7],C[14])+SUMIF(C[7],RC[7],C[15])" & Chr(10) & "+SUMIF(C[7],RC[7],C[16])+SUMIF(C[7],RC[7],C[17])"

.Value = .Value

End With

Application.ScreenUpdating = True

End Sub

Hi please could you help with a macro I’m getting an error with , I’m trying to add a formula so it drags down to the last active row

Range(“C5,L5”).Select

Selection.AutoFill Destination:=Range(“C5:C” & Range(“B” & Rows.count).End(xlUp).Row)

Range(Selection, Selection.End(xlDown)).Select

Last rows to go down to would be column B and I’m trying to drag down c5 to l5 down to last row

Hello Scott – –

Just to clarify please, you want formula(s) to be copied down to the row of last used cell in column B? That’s no problem but I want to first make sure I understand…

• if the formula to be copied down is only in column C.

or

• if the formulas to be copied down are in columns C, D, E, F, G, H, I, J, K, and L.

and

• if the first row of the formula(s) is row 5.

Hi, thanks for the reply , yes the formulas are in columns C, D, E, F, G, H, I, J, K, and L all need to be drag down to what ever the last row is in column b.

All the formulas are in row 5

You can do it with this one-liner:

`Range("C5:L5").Copy Range("C6:L" & Cells(Rows.Count, 2).End(xlUp).Row)`

Hi,

thanks, but it didn’t seem to work it’s says copy method of range class failed.

Before this end with

“Activecell.offset(0, -6).range(“A1:J1”). Select”

Do I need to put anything in between these?

Yes it does work, I tested it before posting it.

I do not understand why you have this in your last reply, or what relevance it has to the question you asked:

Before this end with

`“Activecell.offset(0, -6).range(“A1:J1”). Select”`

What I wrote was,

“You can do it with this one-liner”

That meant what it said.

Your 3 lines of original code were this:

`Range(“C5,L5”).Select`

Selection.AutoFill Destination:=Range(“C5:C” & Range(“B” & Rows.count).End(xlUp).Row)

Range(Selection, Selection.End(xlDown)).Select

My one line of code did that as you asked about.

`Range("C5:L5").Copy Range("C6:L" & Cells(Rows.Count, 2).End(xlUp).Row)`

Hi

My original formula didn’t work that’s why I needed a new one , this one doesn’t work when I try it .

Describe what “doesn’t work” means. The line of code I posted does work because it does what you sad you wanted, which is to copy formulas in C5:L5 down to the row of the last used cell in column B.

Hey Tom,

In my sheet I am entering data into column A every entry, but not every entry column B. I need Column B to auto fill its last value to match my last row in column A.

Here’s what I have Right now:

Dim LastPopulatedRow As Long

Dim LastInM As Long

‘Find last populated row

LastPopulatedRow = Range(“A” & Rows.Count).End(xlUp).Row

LastInM = Range(“B” & Rows.Count).End(xlUp).Cells

‘Select the rows where formula is to be populated

Range(LastInB & “B” & LastPopulatedRow).FillDown

I’m mostly having trouble using the last input in B for the fill

If you could help or know a better way to to this I’d appreciate it.

Thanks

Not exactly sure what you are saying because this already fills to the last value in column A. Maybe all you need is to change a 2 to a 1 as shown:

Instead of this original line of code…

`LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row`

…change it to this:

`LastRowColumnA = Cells(Rows.Count,`

2).End(xlUp).RowThanks for the reply,

Right now I’m getting a run time error 1004 it says method range of object_Global Fail.

I’ve changed A to K and B to M.

Dim LastRowColumnK As Long

Dim LastRowColumnM As Long

LastRowColumnK = Cells(Rows.Count, 11).End(xlUp).Row

LastRowColumnM = Cells(Rows.Count, 13).End(xlUp).Row

Range(LastRowColumnM & “M” & LastRowColumnK).FillDown

I think it doesn’t know what to use to fill.

This code works:

Dim LastRowColumnK As Long

Dim LastRowColumnM As Long

LastRowColumnK = Cells(Rows.Count, 11).End(xlUp).Row

LastRowColumnM = Cells(Rows.Count, 13).End(xlUp).Row

Range(“M4: ” & “M” & LastRowColumnK).FillDown

it will copy M4 to the last row of K. I need it to copy the last value recorded in M not M4 every time.

Hopefully this makes more sense.

Thanks

Your question is confusing.

You say that some code works but that it does not do what you want which to me means it does not work.

Just answer these questions:

(1) What does column K have to do with column M. By that I mean, which of those 2 columns has more data in it BEFORE you run the macro.

(2) Yes or No: Is there a formula in cell M4 that is ALREADY EXISTING and you want to copy it down column M until it reaches a certain row?

(3) If the answer to (2) is Yes, then what determines what that certain row is?

(4) If the answer to (2) is No, then you have totally lost me.