Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for empty cells in Print Area?
Is there a way to check the Print Area for empty cells (or at least no
visible value) so I don't print blank sheets? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for empty cells in Print Area?
Did you manually set the print area, or do you mean if you don't set the
print area and just print the page. In any event, do you mean check it with a macro or what? -- Regards, Tom Ogilvy "Ed" wrote in message ... Is there a way to check the Print Area for empty cells (or at least no visible value) so I don't print blank sheets? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for empty cells in Print Area?
I'm wanting to print using a macro. Usually, all I adjust in the print area
is the margins. I then do a Print Preview and see what I've got. Many times, the creator has modified a previous workbook, or copied a previous worksheet as a "Template", and there may be a piece of border or an unused formula that creates a blank page in the Print Preview. I'm wondering if there's a way to "capture" the range shown in a Print Preview and check it for cells with no visible value. In essence, do by macro what I would visually and manually - if I can't see anything, don't print that page. Ed "Tom Ogilvy" wrote in message ... Did you manually set the print area, or do you mean if you don't set the print area and just print the page. In any event, do you mean check it with a macro or what? -- Regards, Tom Ogilvy "Ed" wrote in message ... Is there a way to check the Print Area for empty cells (or at least no visible value) so I don't print blank sheets? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for empty cells in Print Area?
Hi Ed,
If I understand you correctly it would be a lot better / easier to eliminate the concept of having a Print Area and instead remove the unused rows and columns from the used range. http://www.mvps.org/dmcritchie/excel...m#makelastcell To let Excel find where your lastcell should be you can use Debra's macro at Why do my scrollbars go to row 500 -- my data ends in cell E50?,-- http://www.contextures.com/xlfaqApp.html#Unused You can determine what the printarea is using a macro, I do that on my buildtoc.htm page. http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ed" wrote in message ... I'm wanting to print using a macro. Usually, all I adjust in the print area is the margins. I then do a Print Preview and see what I've got. Many times, the creator has modified a previous workbook, or copied a previous worksheet as a "Template", and there may be a piece of border or an unused formula that creates a blank page in the Print Preview. I'm wondering if there's a way to "capture" the range shown in a Print Preview and check it for cells with no visible value. In essence, do by macro what I would visually and manually - if I can't see anything, don't print that page. Ed "Tom Ogilvy" wrote in message ... Did you manually set the print area, or do you mean if you don't set the print area and just print the page. In any event, do you mean check it with a macro or what? -- Regards, Tom Ogilvy "Ed" wrote in message ... Is there a way to check the Print Area for empty cells (or at least no visible value) so I don't print blank sheets? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for empty cells in Print Area?
David - I looked on your BuildTOC page, but couldn't find where you
reference and use the PrintArea range. In fact, even though Excel help says it returns a range, I can't set a reference to it to use it. But I do notice that when setting up the PrintOut expression, there is a Pages option, which referes to the actual page that would be printed. But I wasn't able to get a reference to it, either. If you have any further info, I would be grateful for the help. Ed "David McRitchie" wrote in message ... Hi Ed, If I understand you correctly it would be a lot better / easier to eliminate the concept of having a Print Area and instead remove the unused rows and columns from the used range. http://www.mvps.org/dmcritchie/excel...m#makelastcell To let Excel find where your lastcell should be you can use Debra's macro at Why do my scrollbars go to row 500 -- my data ends in cell E50?,-- http://www.contextures.com/xlfaqApp.html#Unused You can determine what the printarea is using a macro, I do that on my buildtoc.htm page. http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ed" wrote in message ... I'm wanting to print using a macro. Usually, all I adjust in the print area is the margins. I then do a Print Preview and see what I've got. Many times, the creator has modified a previous workbook, or copied a previous worksheet as a "Template", and there may be a piece of border or an unused formula that creates a blank page in the Print Preview. I'm wondering if there's a way to "capture" the range shown in a Print Preview and check it for cells with no visible value. In essence, do by macro what I would visually and manually - if I can't see anything, don't print that page. Ed "Tom Ogilvy" wrote in message ... Did you manually set the print area, or do you mean if you don't set the print area and just print the page. In any event, do you mean check it with a macro or what? -- Regards, Tom Ogilvy "Ed" wrote in message ... Is there a way to check the Print Area for empty cells (or at least no visible value) so I don't print blank sheets? Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for empty cells in Print Area?
Hi Ed,
find on printarea had no problem finding table, 2nd table below http://www.mvps.org/dmcritchie/excel...toc.htm#sheets or the following line of code: Cells(cRow - 1 + cSht, cCol + 7) = Sheets(cSht).PageSetup.PrintArea But the main point that I want to make is that the use of a Print Area should normally only be used as a last resort, you should be fixing your lastcell indication, so that you aren't printing extra pages. BTW, Print Preview prints everything, there is no selection, at least not in Excel 2000, so the range would be the usedrange that you can find with Ctrl+End MsgBox ActiveSheet.UsedRange.Address(0, 0) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ed" wrote in message ... David - I looked on your BuildTOC page, but couldn't find where you reference and use the PrintArea range. In fact, even though Excel help says it returns a range, I can't set a reference to it to use it. But I do notice that when setting up the PrintOut expression, there is a Pages option, which referes to the actual page that would be printed. But I wasn't able to get a reference to it, either. If you have any further info, I would be grateful for the help. Ed "David McRitchie" wrote in message ... Hi Ed, If I understand you correctly it would be a lot better / easier to eliminate the concept of having a Print Area and instead remove the unused rows and columns from the used range. http://www.mvps.org/dmcritchie/excel...m#makelastcell To let Excel find where your lastcell should be you can use Debra's macro at Why do my scrollbars go to row 500 -- my data ends in cell E50?,-- http://www.contextures.com/xlfaqApp.html#Unused You can determine what the printarea is using a macro, I do that on my buildtoc.htm page. http://www.mvps.org/dmcritchie/excel/buildtoc.htm "Ed" wrote in message ... I'm wanting to print using a macro. Usually, all I adjust in the print area is the margins. I then do a Print Preview and see what I've got. Many times, the creator has modified a previous workbook, or copied a previous worksheet as a "Template", and there may be a piece of border or an unused formula that creates a blank page in the Print Preview. I'm wondering if there's a way to "capture" the range shown in a Print Preview and check it for cells with no visible value. In essence, do by macro what I would visually and manually - if I can't see anything, don't print that page. Ed "Tom Ogilvy" wrote in message ... Did you manually set the print area, or do you mean if you don't set the print area and just print the page. In any event, do you mean check it with a macro or what? -- Regards, Tom Ogilvy "Ed" wrote in message ... Is there a way to check the Print Area for empty cells (or at least no visible value) so I don't print blank sheets? Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for empty cells in Print Area?
Thanks for sticking with me, David.
"David McRitchie" wrote in message ... Hi Ed, find on printarea had no problem finding table, 2nd table below http://www.mvps.org/dmcritchie/excel...toc.htm#sheets or the following line of code: Cells(cRow - 1 + cSht, cCol + 7) = Sheets(cSht).PageSetup.PrintArea Yes, I saw that. But I can't see how that allows you access to the rnage of cells in the PrintArea. I'm sorry if I just don't get it; I admit my undestanding in this area is very limited. It looks to me like you are setting the PrintArea. I'll look at it some more. But the main point that I want to make is that the use of a Print Area should normally only be used as a last resort, you should be fixing your lastcell indication, so that you aren't printing extra pages. The big issue I face is when the text in a cell overflows into the next print area. or there is one small comment line only in the next print area. I don't set up these worksheets - I just get to work with them. My idea was to see if I cold find the rows and columns on the limits of each PrintArea, set a range bounded by those cells, and then check certain cells in that range for content; if there is none, then that is not a useful section to print out and I can quit wasting paper. This would be in a macro that would cycle through each worksheet of a workbook. If this is an impossible idea, then so be it. Sometimes it's just nice to dream. Thanks for the boost. Ed BTW, Print Preview prints everything, there is no selection, at least not in Excel 2000, so the range would be the usedrange that you can find with Ctrl+End MsgBox ActiveSheet.UsedRange.Address(0, 0) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ed" wrote in message ... David - I looked on your BuildTOC page, but couldn't find where you reference and use the PrintArea range. In fact, even though Excel help says it returns a range, I can't set a reference to it to use it. But I do notice that when setting up the PrintOut expression, there is a Pages option, which referes to the actual page that would be printed. But I wasn't able to get a reference to it, either. If you have any further info, I would be grateful for the help. Ed "David McRitchie" wrote in message ... Hi Ed, If I understand you correctly it would be a lot better / easier to eliminate the concept of having a Print Area and instead remove the unused rows and columns from the used range. http://www.mvps.org/dmcritchie/excel...m#makelastcell To let Excel find where your lastcell should be you can use Debra's macro at Why do my scrollbars go to row 500 -- my data ends in cell E50?,-- http://www.contextures.com/xlfaqApp.html#Unused You can determine what the printarea is using a macro, I do that on my buildtoc.htm page. http://www.mvps.org/dmcritchie/excel/buildtoc.htm "Ed" wrote in message ... I'm wanting to print using a macro. Usually, all I adjust in the area is the margins. I then do a Print Preview and see what I've got. Many times, the creator has modified a previous workbook, or copied a previous worksheet as a "Template", and there may be a piece of border or an unused formula that creates a blank page in the Print Preview. I'm wondering if there's a way to "capture" the range shown in a Preview and check it for cells with no visible value. In essence, do by macro what I would visually and manually - if I can't see anything, don't print that page. Ed "Tom Ogilvy" wrote in message ... Did you manually set the print area, or do you mean if you don't set the print area and just print the page. In any event, do you mean check it with a macro or what? -- Regards, Tom Ogilvy "Ed" wrote in message ... Is there a way to check the Print Area for empty cells (or at least no visible value) so I don't print blank sheets? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set print area over locked cells | Excel Worksheet Functions | |||
When printing - ability to not print empty or $0 cells | Excel Discussion (Misc queries) | |||
setting print area to selected cells | Excel Discussion (Misc queries) | |||
eliminating empty cells from chart area | Charts and Charting in Excel | |||
Highlighting a Block of Cells to Define the Print Area | Excel Programming |