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”).SelectSelection.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)Tom, You Rock!
I stumbled on to your “short & sweet” solution which is just what I needed:
My adaptation of your code follows:
Range(“G2:J2”).Copy Range(“G3:J” & Cells(Rows.Count, 2).End(xlUp).Row)
Fills my 2500 row table in the blink of an eye.
thank you.
Thank you, Wayne!
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.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
‘Sub Myautofill()
If Range(“A1”) “” Then
Range(“A1”).Select
Selection.AutoFill _
Destination:=Range(“A1:A5”), Type:=xlFillDefault
‘End With
‘Range(“A1”).Formula = “=IF(AND(F5=””,G5=””,H5=””),””,(I4+F5-G5-H5))”
End If
‘End With
End Sub
ols correct me
There are a couple of competing actions happening in your code. Please explain with words what you are trying to accomplish, and especially, say what an example is in cell A1 and what results after the macro would be in range A2:A5. I can’t tell what exactly it is that you want to do.
I am trying to autofill column G based on the last row on column A by using this code:
Dim lastRows As Long
lastRows = Range(“A” & Rows.Count).End(xlUp).Row
Range(“G2”).AutoFill Destination:=Range(“G2:G” & lastRow)
However, earlier in the macro column A had more rows which were deleted. The above macro is autofilling down to the orignal row number before the rows were deleted. How do I get the macro to just see what is currently occupying column A?
Are you 100% positive that there is no lingering item in any cell in column A.
Is there a reason why you are using AutoFill. What is in cell G2 that you ar efilling down… a formula, or text, or a number series? There might be another way to do this depending on what you are working with.
Hello,
Can you please help me with this macro below?
ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-8]:R[27]C[-8],CPQ.txt!C1:C3,3,FALSE)”
Range(“M3”).Select
Selection.AutoFill Destination:=Range(“M3:M30”)
I want to autofill column M until the last active row in E.
Assuming your VLOOKUP function works as you presented it here, and further assuming your VLOOKUP formula is first entered into cell M2, then this is what you want:
Range("M2:M" & cells(rows.count, 5).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-8]:R[27]C[-8],CPQ.txt!C1:C3,3,FALSE)"Thank you very much! It worked 🙂
Hello,
I would be very glad if you could help me.
I want to add a filter according to the value on “F3” (it will change but the right now value is equal to “2S24J”) but the I could only add the filter as a constant value not a variable.
Windows(“CPQ.txt”).Activate
ActiveSheet.Range(“$A:$D”).AutoFilter Field:=2, Criteria1:=”2S24J”
Could you not just do this?
ActiveSheet.Range(“$A:$D”).AutoFilter Field:=2, Criteria1:=Range(“F3”).Value
Nope, it doesn’t work.
What does “it doesn’t work” mean. Do you get a run time error or nothing gets filtered, or the wrong rows get filtered.
Assuming your cell F3 is on the same worksheet as the filter, and assuming there are no merged cells involved, try a variable declaration:
Dim strFilterCriteria as String
strFilterCriteria = Range(“F3”).Value
ActiveSheet.Range(“$A:$D”).AutoFilter Field:=2, Criteria1:=strFilterCriteria
It could be that your range to filter should be defined better than just the full columns, such as Range(“A1:D1000”), and also, I do not see where you specify the first row of the range being filtered.
Sorry, I forgot to explain the error.
Yes, it is on the same worksheet and no merged cells involved.
I have tried new one(Dim strFilterCriteria as String
strFilterCriteria = Range(“F3”).Value
ActiveSheet.Range(“$A:$D”).AutoFilter Field:=2, Criteria1:=strFilterCriteria)
it gives the run time error. when I click on debug, it highlights the line which contains “strFilterCriteria = Range(“F3”).Value”
You can see the error below:
Run-time error’1004′.
Method ‘Range’ of object ‘_Global’failed.
Please let me know if you need further information
As I said, I would specify the actual range instead of the full columns, and I asked if any cells are merged. Maybe what cell F3 visually shows is really an underlying value that the filter does not like, such as a custom format. Something is going on with either the filter range or the criteria cell.
Dear Tom,
I am not a member of any forum so I, not sure if I am entitled to ask you for guidance. But just in case you agree to offer your guidance, here is my problem:
I am quite familiar with filling down a series with reference to the number of rows in another column. What I am looking for is code that enables me to fill a series down to a maximum amount specified (without reference to any other column).
For example, if I start with an entry in say cell D5: D4*0.8, with cell D4 showing say 15000, then fill down the series, what code should I use to make the series stop before it gets below say 1000?
If you do agree to offer your help, will I receive an email or will it appear on this website?
Kind regards,
Zarir Suntook
Regarding your example scenario, there must be some way for Excel to know that the series should stop before it gets to row 1000. Unless you mean that the example of 1000 is not related to a row number but to the formula’s return result number. So that important piece of information is not clear from your message.
Sorry Tom, I should have made it clear that the 1000 in my example relates to the formula’s return result number – NOT the row number (for which I’m able to do the coding).
No problem, thanks for clarifying.
If it were me, I would approach this one of 2 ways.
If I knew that your example was really the case, that it can be 1500 formulas or more, then I would fill the range with some reasonably excessive formulas that I knew would surpass the limit, then search for the first cell that exceeds the limit and delete the formulas from that cell on.
The other option is if it would only be a relatively few cells, I would enter the formulas in a loop up to the point where the formula’s pre-evaluated limit would not be entered.
All possible with VBA, but that is how I would approach this, just my take.
Many thanks, Tom, for your suggestions. I have in fact already used your first option, which works fine, except that as you say I need to set a range that I believe would exceed the designated limit and then delete the following formulas.
I wonder, however, if you could please give me the VBA code for your second option that would apply to my example, ie to enter the formulas in a loop up to the point where the formula’s pre-evaluated limit (1000 in my example) would not be entered. That really is where my VBA knowledge is lacking. Many thanks for all your time.
Assuming cell D4 contains the number 100, this is one way, albeit a bit clunky-looking but more efficiant than cell by cell pre-evaluations.
Sub Test1()Application.ScreenUpdating = False
Dim NumberLimit As Double, lngRow As Long
NumberLimit = 10
lngRow = 4
Do
lngRow = lngRow + 1
Cells(lngRow, 4).FormulaR1C1 = "=R[-1]C*0.8"
Loop Until Cells(lngRow, 4).Value < NumberLimit With Cells(lngRow, 4) If .Value < NumberLimit Then .ClearContents End With Application.ScreenUpdating = True End Sub
That’s brilliant, Tom – it works perfectly and I’ve learnt something valuable about VBA Looping code.
Kind regards,
Zarir
PS: I now know whom to come to when I need help!
Hi Tom,
I’m trying to autofill H-J columns with a formula that trims A-C columns data (till its last row) Can you help me?
You can do this several ways depending on how populated (or not) your data is in columns A-C. For example, Maybe column A data goes down to row 125, column B data goes down to row 2548 and column C data goes down to row 7. In that case you would only want the respective columns H-J to compile for their relevant rows. That solution would be a loop.
Or, maybe all 3 columns end in the same row, in which case a loop would not be needed because the last row would be common to every column.
So what is the case with what you are working with, and what is your trim formula.
Hello,
I am not sure how active this sub is anymore. I have a somewhat similar situation to what your example is. I am pulling data from another worksheet and then just dragging it down manually, I was wondering if there is a way to do it with VBA. The issue is the amount of data varies so I cannot just set it as a number. I was trying to use your macro, but I am not good enough to figure out how to do it from pulling from another sheet. I am pulling data from the other sheet and placing them in columns A-H, and then was manually dragging them down x amount of spots. The sheet I am pulling data from is called Detail.
Thank you,
Lucas
It sounds like what you need instead of a macro, such as I posted, is a Worksheet Change event that would be placed into the module of the destination worksheet where columns A:H were pasted. More detail is needed, such as where the formula is that you need to copy down automatically (for example maybe the formula is in column I or J or somewhere outside of columns A:H), and also what the formula is, and in what cell that formula is first entered into. With that detail known, the Worksheet Change event, triggered by the paste into A;H, would fill down this formula as far as the data exists in A:H.
Hi,
I have problem in replacing the “LastRowColumnA” value to a defined “number of rows”, let say n, which user will enter manually.
For example, I want the formula change by row, where if my n=18
Range(“AJ5:AJ” & nRow).Formula = “=MAX(IF(C1=””n””,C[-8]),)”
Row 1:
Range(“AJ5:AJ” & nRow).Formula = “=MAX(IF(C1=””1″”,C[-8]),)”
Row2:
Range(“AJ5:AJ” & nRow).Formula = “=MAX(IF(C1=””2″”,C[-8]),)”
and so on until Row 18.
Thank you,
Hexa
In what method or by what action does the user take to specify a number of rows? Is it entered into a cell? If so which cell? Is it in an input box? Is it a random number? How?
User enter the value into a cell. In my example, value n=18 is entered into cell “AK2”.
Sorry if my question is not clear.
would using loop be my best solution?
You do not need to program for a formula if all you want to do is have the MAX number be in some cell, for example cell AJ1.
Range(“AJ1”).Value = “=MAX(C5:C” & Range(“AK2”).Value & “)”