Coping To An Unknown Row!

[Rob]

Excel 2000

I need to copy a formula in cell C3 down to the last row of data in Column
B?
I have a range in column B that charges size ie. the number of rows vary,
but always starts in B3. I then has a single formula in cell C3 which needs
to be copied down to the last row of the range in column B.

The following works so long as the range in column B end at row 180. How
can I set the range each time the code runs?

Thanks, Rob

Sub Macro1()
Range("B3:C3").Select
Range("C3").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("C3:C180").Select 'THIS needs to be a variable
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
End Sub

 

[mudraker]

Rob

Try this

Sub Macro1()
Dim R As Long
R = Cells(Rows.Count, "b").End(xlUp).Row
Range("C3").AutoFill Destination:=Range("C3:C" & R)
Type:=xlFillDefault
Range("C3:C" & R).Copy
Range("C3:C" & R).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Su

 

[Trevor Shuttleworth]

Rob

one way:

Sub Macro1()
Dim OriginalCell As Range
Dim rng As Range
Set OriginalCell = ActiveCell
Set rng = Range("C3:C" & _
Range("B" & Rows.Count).End(xlUp).Row)
With rng
.ClearContents
Range("C3").Formula = "=sum(A3:B3)"
.FillDown
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False
OriginalCell.Select
End Sub

Regards

Trevor

 

[donstock]

Rob,
Don't know how to get this to paste values only, but this should paste all.
Maybe it will be of some help to you. I think you can replace all with this
single line.

Range("C3").Copy Range("B3", Range("B3").CurrentRegion.End(xlDown))

 

[Rob]

Thanks everyone, excellent solutions.

Regards, Rob.