Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dragging a range of cells
I am working on a spreadsheet with a variable number of columns. It is
a financial projection spreadsheet (retirement planning) of high complexity. With 100 columns (i.e. a 100 year projection), the spreadsheet is both huge and slow. I don't think I always want only 100 columns though. I'm trying to create a version which starts with a more manageable number of years, i.e. 5, which can then be extended with a macro. This way I would have a file of only a few hundred k to distribute, which would run quickly, and if someone wants to create a 10 megabyte 100 column monster out of it, its their choice. If I want to drag the range F8:F12 right ten columns, I would use this code: Range("$F$8:$F$12").Select Selection.AutoFill Destination:=Range("F8:O12"), Type:=xlFillDefault Easy enough. A recorded macro does that. But I want to drag by a variable number of columns. If I want to project for 10 years, I would have a cell with the number 10 in it and a button next to it saying "Go" or something. My number of years to project is in a cell specified by Range("projectionyears").Value And I don't actually want to specify my range to extend from as Range("$F$8:$F$12"), the range was actually called Range("startingcolumn"). So what I want to know, is how to take the range Range("startingcolumn") and extend this right by Range("projectionyears").Value columns. Travis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dragging a range of cells
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dragging a range of cells
With Range("startingcolumn")
.AutoFill Destination:= _ .Resize( ,Range("projectionyears").Value), _ Type:=xlFillDefault End With -- Regards, Tom Ogilvy "travis" wrote: I am working on a spreadsheet with a variable number of columns. It is a financial projection spreadsheet (retirement planning) of high complexity. With 100 columns (i.e. a 100 year projection), the spreadsheet is both huge and slow. I don't think I always want only 100 columns though. I'm trying to create a version which starts with a more manageable number of years, i.e. 5, which can then be extended with a macro. This way I would have a file of only a few hundred k to distribute, which would run quickly, and if someone wants to create a 10 megabyte 100 column monster out of it, its their choice. If I want to drag the range F8:F12 right ten columns, I would use this code: Range("$F$8:$F$12").Select Selection.AutoFill Destination:=Range("F8:O12"), Type:=xlFillDefault Easy enough. A recorded macro does that. But I want to drag by a variable number of columns. If I want to project for 10 years, I would have a cell with the number 10 in it and a button next to it saying "Go" or something. My number of years to project is in a cell specified by Range("projectionyears").Value And I don't actually want to specify my range to extend from as Range("$F$8:$F$12"), the range was actually called Range("startingcolumn"). So what I want to know, is how to take the range Range("startingcolumn") and extend this right by Range("projectionyears").Value columns. Travis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dragging a range of cells
Tom Ogilvy wrote: With Range("startingcolumn") .AutoFill Destination:= _ .Resize( ,Range("projectionyears").Value), _ Type:=xlFillDefault End With Thanks Tom, that's a very elegant solution. Travis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dragging values down a range using a colum to determine its length | Excel Worksheet Functions | |||
How to maintain a set range of cells when dragging a formula (e.gmonthlyannual data) | Excel Discussion (Misc queries) | |||
Dragging a range | Excel Worksheet Functions | |||
Excel range selection as if dragging, keyboard not responding | New Users to Excel | |||
Dragging range (array) | Excel Discussion (Misc queries) |