Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autofill variable lenght column w/ sequential numbers using VBA | Excel Discussion (Misc queries) | |||
variable column numbers for autofill | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Variable Autofill range | Excel Programming | |||
Variable Autofill Range | Excel Programming |