PDA

View Full Version : Autofilling a formulae to lastrow



JZB
04-27-2009, 01:44 AM
Hi Guys

I am currently writing very simple formula loops but the data is very large so can take up a lot of time and memory.

Is there a way i can incorporate the lastrow variable into the autofill function? this is what i am writing but it is not happy :banghead:

Range("K2").Select
ActiveCell.FormulaR1C1 = "=ABS(RC[-3])"
Range("K2").Select

nlastrow = Range("A65536").End(xlUp).Row

Selection.AutoFill Destination:=Range("K" & nlastrow)

Cheers

Jon

Trebor76
04-27-2009, 02:12 AM
Hi Jon,

Post back re how the following goes:


Range("K2").Formula = "=ABS(H2)"
Range("K2").Copy _
Range("K3:K" & Cells(Rows.Count, "A").End(xlUp).Row)

HTH

Robert

MaximS
04-27-2009, 02:24 AM
or just:


Range("K2:K" & Cells(Rows.Count, "H").End(xlUp).Row).Formula = "=ABS(H2)"

JZB
04-27-2009, 02:42 AM
very good stuff. appreciate the help:bow:

mdmackillop
04-27-2009, 05:28 AM
Hi Jon,
While I would go with Maxim's version, you were not far away with your code. You need to use the absolute form of R1C1

Range("K2").Select
ActiveCell.FormulaR1C1 = "=ABS(R2C8)"
Range("K2").Select
nlastrow = Range("A65536").End(xlUp).Row
Selection.AutoFill Destination:=Range("K2:K" & nlastrow)