Autofill variable range
 

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

Autofill variable range
 

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

Autofill variable range
 

Peter T,

Thnx for the tip. Sub Test 2 did the trick!

Regards

Basta1980

"Peter T" wrote:

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