Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing Discontiguous Ranges
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 -- GoFigure ------------------------------------------------------------------------ GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274 View this thread: http://www.excelforum.com/showthread...hreadid=493043 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing Discontiguous Ranges
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. -- Regards, Tom Ogilvy "GoFigure" wrote in message ... 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 -- GoFigure ------------------------------------------------------------------------ GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274 View this thread: http://www.excelforum.com/showthread...hreadid=493043 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing Discontiguous Ranges
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 Tom Ogilvy Wrote:[color=blue] 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" wrote in messag ... 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 th 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 workshee itself, i.e., without VBA code, this is fairly straightforward. Hide al 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 togethe 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 -- GoFigure /QUOTE -- GoFigur ----------------------------------------------------------------------- GoFigure's Profile: http://www.excelforum.com/member.php...nfo&userid=427 View this thread: http://www.excelforum.com/showthread.php?threadid=49304 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing Discontiguous Ranges
Does anyone have a, or some, suggestions? I'd appreciate any. Many thanks, - Al GoFigure Wrote: 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("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 totals -------------------- This results in two pages: - Page 1 - Q1 totals, Apr, May, June - Page 2 - Q2 totals - Al -- GoFigure ------------------------------------------------------------------------ GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274 View this thread: http://www.excelforum.com/showthread...hreadid=493043 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing Discontiguous Ranges
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??). GoFigure wrote: Does anyone have a, or some, suggestions? I'd appreciate any. Many thanks, - Al GoFigure Wrote: 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("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 totals -------------------- This results in two pages: - Page 1 - Q1 totals, Apr, May, June - Page 2 - Q2 totals - Al -- GoFigure ------------------------------------------------------------------------ GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274 View this thread: http://www.excelforum.com/showthread...hreadid=493043 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deselect a cell in a discontiguous range | Excel Discussion (Misc queries) | |||
Is it possible to plot discontiguous cells? | Charts and Charting in Excel | |||
Is it possible to plot discontiguous cells? | Excel Discussion (Misc queries) | |||
discontiguous ranges in charts | Excel Programming | |||
Defining a discontiguous Range object | Excel Programming |