Print selective pages - only if the pages's Rows.Hidden = False"

[Marie J-son]

Hi there,
I have a sheet with 25 pages, where the ranges of the pages are named
ranges. I have a different number of pages to print out, from only 3 to all
25. I find that the solution should become like:

Sheet1.PageSetup.PrintArea = Sheet1.Range(Union(rng1, rng2, rng3,
....)).Address

But how to come there? In words:
Identify the pages with "Rows.hidden = False". If True, the name of the page
should be added to an array.
After looping through the sheet, there is a array to add into the Union
formula

Am I right? If yes - how do I do this in vba? My weakest point in VBA is
Arrays, and I can't figure out how to compare/match with the named ranges of
the pages.

Maybe you got an other solution on the shelf with an other approach?

Please, help me

/Regards

 

[Tom Ogilvy]

You can build your union progressively

for i = 1 to 10
bPrint = true
for each cell in Thisworkbook.Names(i).ReferestoRange
if cell.EntireRow.Hidden = True then
bPrint = False
exit for
end if
Next
if bPrint then
set rng1 = Thisworkbook.Names(i).ReferstoRange
if rng is nothing then
set rng = rng1
else
set rng = union(rng, rng1)
end if
end if
Next
if not rng is nothing then
Activesheet.PageSetup.PrintArea = rng.Address
End if