Can you use Cells instead of Range inside of an Au

Question

If I have this, for example:

Sub ExampleThing()

    Counter = 13
    For i = 1 To Counter
        Range("A" & i) = Rnd
    Next

    Range("B1").Formula = "=(PI()*A1)"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B" & Counter), Type:=xlFillDefault

End Sub

What if, instead of using Range() under Destination, I wanted to use Cells()? Like, instead of having cell references inside the range function, replace that with Cells(), like this:

Selection.AutoFill Destination:=Range("Cells(1,2):Cells(Counter,2)"), Type:=xlFillDefault

I've been playing around with it, and can't seem to get it to work.

Answer 1

You're very close. Here's a version with your variables declared (which you really should do) and the Select statements eliminated (also a good practice):

Sub ExampleThing()
Dim Counter As Long
Dim i As Long

Counter = 13
For i = 1 To Counter
    Range("A" & i) = Rnd
Next
Range("B1").Formula = "=(PI()*A1)"
Range("B1").AutoFill Destination:=Range(Cells(1, 2), Cells(Counter, 2)), Type:=xlFillDefault
End Sub