Code for Printing Discontiguous Ranges

[GoFigure]

Excel version: 11 (2003 SP2)
OS: WXP SP2

I have a worksheet that has monthly, quarterly and year columns. The
rows are various items whose sales figures are recorded in the cells.
The columnar layout is the three months of a quarter followed by the
quarter summary.

For the yearly summary report, I want to print each quarter and the
year totals adjacent to each other. Operating on the worksheet itself,
i.e., without VBA code, this is fairly straightforward. Hide all months
and print the area with the quarter and year columns.

When I specify a print area in VBA as, for example, ThisPrintArea =
"Q1, Q2, Q3, Q4, Year", where Qx and Year are non-adjacent ranges, I
get five pages instead of one. Each page has the horizontal and
vertical repeating rows with one range on each page.

I know I can change the layout so that all the months are together and
all the quarters plus the year are together but I'd like to know how
one accomplishes the desired result in VBA.

Thanks,

- Al

 

[Tom Ogilvy]

Same as manually. Hide the monthly columns, print the whole rectangular
area (one single range which includes all the data to print), unhide the
monthly columns.

 

[GoFigure]

Perhaps I'm not coding this correctly. I have tried your sugestion, Tom
but it still results in all the columns in the range, including th
hidden ones.


Code
-------------------
Sheets(sThisSheetName).Columns("B:J").Hidden = True ' Hide Jan - Mar
Sheets(sThisSheetName).Columns("M:U").Hidden = True ' Hide Apr - Jun
PA_PrintRange = "$K10:$W52" ' K:L = Q1 totals and V:W = Q2 total
-------------------


This results in two pages:

- Page 1 - Q1 totals, Apr, May, June
- Page 2 - Q2 totals


- Al

Same as manually. Hide the monthly columns, print the whol
rectangular
area (one single range which includes all the data to print), unhid
the
monthly columns.

--
Regards,
Tom Ogilvy


"GoFigure" <[email protected]
wrote in
messag

 

[GoFigure]

Does anyone have a, or some, suggestions? I'd appreciate any.

Many thanks,

- Al

Perhaps I'm not coding this correctly. I have tried your sugestion, Tom,
but it still results in all the columns in the range, including the
hidden ones.
Code:
--------------------
Sheets(sThisSheetName).Columns("M:U").Hidden = True ' Hide Apr - Jun
PA_PrintRange = "$K10:$W52" ' K:L = Q1 totals and V:W = Q2 totals --------------------

This results in two pages:

- Page 1 - Q1 totals, Apr, May, June
- Page 2 - Q2 totals

 

[Dave Peterson]

Make sure you don't have any manual page breaks in that range (even in the
hidden columns).

And make sure your set the print to only one page wide (or one page tall??).