Setting a print range using variables
 

I would like to write a macro using VBA to set print ranges
dynamically. I have several macros that create spreadsheets based
upon the number of rows in the data, which I store in a range. I can
copy formulas down and format the data using offsets, but I cannot
figure out how to set the print area using VBA code. If I record a
macro, it requires an absolute reference. Is it possible to use a
range name or variable to set the print area? If not, is there
another way to format my report to print to one page, without manually
changing it?

I'm using Excel 2000 on Win2K and WinXP machines. I can post some
sample code if necessary, let me know what you'd like to see.

Setting a print range using variables
 

As far as I know Range() can't use variables. I have
done some variables with using Cells() instead.
ex. Sheet1.Cells( i, 4).Value = "Hello"
where i is the row and 4 is the column.

I'm sure you could get the printing to work using Cells()
rather than Range().


-----Original Message-----
I would like to write a macro using VBA to set print
ranges
dynamically. I have several macros that create
spreadsheets based
upon the number of rows in the data, which I store in a
range. I can
copy formulas down and format the data using offsets,
but I cannot
figure out how to set the print area using VBA code. If
I record a
macro, it requires an absolute reference. Is it
possible to use a
range name or variable to set the print area? If not,
is there
another way to format my report to print to one page,
without manually
changing it?

I'm using Excel 2000 on Win2K and WinXP machines. I can
post some
sample code if necessary, let me know what you'd like to
see.
.

Setting a print range using variables
 

a couple of examples of using variables with range


Range("a1:d" & myval).Select ' myVal = row number
Range("a1:" & MyCol & MyRow).Select 'MyCol = Column letter, MyRow
row number




myval = ActiveCell.SpecialCells(xlLastCell).Address
Range("a1:" & myval).Selec

--
Message posted from http://www.ExcelForum.com

Setting a print range using variables