Thread
:
Dragging a range of cells
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
Dragging a range of cells
try using something like
mx=range("a10")
range("f8:f12").autofill destination:=range(cells(8,"f"),cells(12,mx))
--
Don Guillett
SalesAid Software
"travis" wrote in message
ps.com...
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett