Copy and paste in VBA

[AM]

I want to copy the formula in E7 down to the number of
rows that contain data in columns A through D. But this
number may vary. In this example the last row with data
will be 12,073. How can I get VBA to take this into
account?


Range("E7").Formula = "=Sum(B77)"
Range("E7").Copy Destination:=oWorksheet.Range("E8:E12073")

 

[Ron de Bruin]

Hi AM

This example will filldown till the last value in D

Sub test()
Dim LastRow As Long
With Worksheets("Sheet2")
.Range("E7").Formula = "=Sum(B77)"
LastRow = .Cells(Rows.Count, "D").End(xlUp).Row
.Range("E7").AutoFill Destination:=.Range("E7:E" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

 

[Tom Ogilvy]

Dim rng as Range
set rng = cells(rows.count,4).End(xlup)(1,2)
Range(range("E7"),rng).Formula = "=Sum(B77)"


as an example in the immdiate window:
set rng = cells(rows.count,4).End(xlup)(1,2)
? range(range("E7"),rng).Address
$E$7:$E$20