Print selection, based on variable range of rows.

[drjpb]

Hi,
I am unable to write a macro to print a sheet consisting of variable rows.

The sheet (SheetX) i want to print consists of 500 rows containing formulas (cells are linked to another sheet (SheetY) in same workbook).
So if SheetY have only 350 rows, i want the macro to print the range (A1:C350) in SheetX.

I believe i have to use the Count function in SheetY to specify the number of rows in SheetX, which then will be used in a macro.Thanking you in advance.

 

[jolivanes]

lr = Sheets("SheetY").UsedRange.Rows.Count

Set the PrintRange to

Range("A1:C" & lr)

 

[MARK858]

You need to add .Address to the end as PrintArea uses a String not a range i.e.

lr = Sheets("SheetY").UsedRange.Rows.Count
Sheets("SheetX").PageSetup.PrintArea = Sheets("SheetX").Range("A1:C" & lr).Address

 

[jolivanes]

Hi Mark
You're right (again).

 

[drjpb]

Thank you jolivanes & MARK858, works great.