Setting a print range using variables

  • Thread starter Thread starter Mary Branson
  • Start date Start date
M

Mary Branson

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.
 
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().
 
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
 
I would like to write a macro using VBA to set print ranges
dynamically.
Click to expand...

Here's the code I finally used (I will substitute variables in for the numbers):

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(22, 50)).Address

Thanks for all the help!
 
Back
Top