jljonlac440
New Member
- Joined
- Jan 19, 2010
- Messages
- 26
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
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