VBA to copy down formula to last row of data

[jljonlac440]

Hi All, I have a large spreadsheet with thousands of rows (Using Excel 2007)

I need to copy down a formula from cell I2 to the last row that contains data (the number of rows will vary from month to month), is there a quick way of doing this rather than dragging the formula down (which can take a while depending on the number of rows).

I have a pice of code below which copies the cell above but just copies the value & not the formula

Private Sub CommandButton3_Click()
Dim MyRange As Range, c As Range
On Error Resume Next
Set MyRange = Range("I:I").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not MyRange Is Nothing Then
MyRange.FormulaR1C1 = "=R[-1]C"
For Each c In MyRange
c.Value = c.Value
Next c
End If
End Sub

Thanks in advance for your help.

Kind Regards
J

 

[bolandd]

Did you try double clicking the lower right hand corner of the cell with the formula in it? It causes the formula to copy down to the first blank cell..

 

[VoG]

Try

Private Sub CommandButton3_Click()
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Range("I2").AutoFill Destination:=Range("I2:I" & LR)
End Sub

 

[Peter Thompson]

You can use the code below to deteremine the lastrow

dim lastrow as long
long = cells(rows.count,1).end(xlup).row

This will look in column a and work out how many rows there are. If column a doesn''t go to the bottom of the data then change the number after "rows.count," to the column number

You can then apply the formula as follows:

Range("i2:i" & lastrow).formular1c1 = "yourformula"

Peter

 

[jljonlac440]

Thanks guys, they all work. Knew about double clicking the bottom right hand corner but am trying to up my VBA skills (which is going to take me an age!!!)

 

[tomjamieson]

This was very helpful for me. I'm using Excel 2010, so I had to kinda of morph the two solutions into one, but it worked! Thanks!