Autofill variable range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Basta1980 - 19 Mar 2008 09:41 GMT

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

Reply to this Message

Peter T - 19 Mar 2008 10:38 GMT

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

> Hi,
>
[quoted text clipped - 17 lines]
> Range("B1:B316").Select
> End Sub

Reply to this Message

Basta1980 - 19 Mar 2008 11:32 GMT

Peter T,

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

Regards

Basta1980

> various ways -
>
[quoted text clipped - 47 lines]
> > Range("B1:B316").Select
> > End Sub

Reply to this Message