various ways -

With ActiveSheet.Range("B1")
.AutoFill .Offset(0, -1).CurrentRegion.Columns(2)
End With

The above would only work correctly if A1 was in the top row of its
CurrentRegion, which in this case it must be as A1 is clearly the top row.

If that can't be guaranteed here's another way

Sub Test2
Dim rng As Range
Set rng = ActiveSheet.Range("B2")

rng.AutoFill Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1))

End Sub

BTW, no need to 'select' cells

Regards,
Peter T



"Basta1980" wrote in message
...
Hi,

I try to wright a command which autofills a range adjacent to information
in
the cells left. So if Range A1:A300 contains data and I have a formule in
cel
B1 I normally double click the fill-handle to copy (or autofill) the
formula
down to cel B300 (which is adjacent to cel A300). The below statement
helps
me manage to automatically autofill the range (B1:B300). My problem is
that
range A can be variable, so this time it's up to cel A300, the following
time
it's up to cel A500. How should i adjust the statement?!

Sub test()
'
' test Macro
' Macro recorded 19-03-2008 by dresses
'

'
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B316")
Range("B1:B316").Select
End Sub