Creating a variable print area, ignoring formulas.

[supermanjohn01]

As simple as I can put it, I have created an excel worksheet that is an invoice. Using formulas, the invoice, (we'll call worksheet A), pulls information from another work sheet, (worksheet B), but if the information is blank on worksheet B, then it is also SHOWING blank on worksheet A, although the formula is there, (=IF(worksheet B="",""). I only want the rows printed on the invoice that are no longer showing blank cells, drawn from worksheet B. The current information I find online is still trying to print the cells that have a formula, but are not showing blank. I need DETAILED help. Assume I know nothing. The name of my invoice is Contract Invoice. The area parameters are A1:M76. Nothing I have tried has worked. I don't know if this is clear or not, but I need help from someone that REALLY knows what they are doing. Thanks in advance.

 

[6StringJazzer]

You also need to assume we know nothing about your worksheet. Give a detailed description of your data layout including which cells could have formulas but be blank.

Here is the strategy you are going to use. When you set a print area for a worksheet, Excel automatically creates a named range called Print_Area that simply gives the range for the print area. You can change this to be a formula. You can create a formula that determines the non-blank cells and uses that as the print area. For example, suppose you had a sheet with formulas in column A. The formulas go down to row 76, but a variable number of the last ones could be blank. The Print_Area range could be defined as:

=$A$1:INDEX($A:$A,COUNTIF($A:$A,"*?"))

This will count the number of non-blank cells in column A, and that number will be the last row to be printed.

But I cannot give you the actual formula for your own worksheet without knowing the details of what your worksheet looks like, so I know how to determine what the print area should be under different conditions.

 

[supermanjohn01]

Here is a screen shot of the sheet. You can see I have one row that has information that was added on the other sheet. As I add to the other sheet, it will add a row of cells underneath. I only want it to print down through the visible rows. The rows that you can't see are full of formulas. Everything I've tried so far is still reading the formulas, so it is still trying to print three pages, with two of them being blank. The name of this sheet is Contract Invoice, if it printed with everything filled in, the cell ranges would be A1:N76. From row 11 down to 76 is the section that adds more rows as I add more information.

 

[6StringJazzer]

You did not provide the sheet name.

Set a print area for your worksheet. It doesn't matter what. Select a range of cells then
Page Layout > Print Area > Set Print Area

Go to Formulas > Name Manager. Find the name Print_Area where under the Scope column it has your worksheet name. Click on it to select.
Edit the formula to be

='Sheet Name'!$A$1:INDEX('Sheet Name'!$N:$N,10+COUNTIF('Sheet Name'!$C$11:$C$76, "*?"))

Change Sheet Name to your actual sheet name.

Unfortunately I cannot test this for you on a screen shot. Let me know if it works for you.

 

[supermanjohn01]

You did not provide the sheet name.

Set a print area for your worksheet. It doesn't matter what. Select a range of cells then
Page Layout > Print Area > Set Print Area

Go to Formulas > Name Manager. Find the name Print_Area where under the Scope column it has your worksheet name. Click on it to select.
Edit the formula to be

='Sheet Name'!$A$1:INDEX('Sheet Name'!$N:$N,10+COUNTIF('Sheet Name'!$C$11:$C$76, "*?"))

Change Sheet Name to your actual sheet name.

Unfortunately I cannot test this for you on a screen shot. Let me know if it works for you.

View attachment 101289

First off, thank you so much for your help, however, it doesn't seem to be setting a print area. I can't see the outline for the print area, and then when I went to the print screen it is still trying to print 3 pages, so it seems its still recognizing the cells with formulas. I have attached a screen shot. And the name of the the sheet is Contract Invoice.

 

[6StringJazzer]

You seem to have some random data beginning with 1256. That won't cause the problem you see but my solution depends on expecting that all rows starting in row 11 will be contiguous (i.e., no gaps).

You didn't show how you defined the name, as I illustrated, so I can't be sure that you implemented the solution correctly.

There are only two ways for me to help further. 1) You give me a copy of your file and I diagnose and fix it, or 2) I provide a file that I mock up as an example, then you have to compare it to your file to see what's different.

If you don't have a way to share your file I'll do #2 but it will take a little time.

 

[6StringJazzer]

BTW I see on second look you are using merged cells, which I highly discourage.

 

[supermanjohn01]

You seem to have some random data beginning with 1256. That won't cause the problem you see but my solution depends on expecting that all rows starting in row 11 will be contiguous (i.e., no gaps).

You didn't show how you defined the name, as I illustrated, so I can't be sure that you implemented the solution correctly.

There are only two ways for me to help further. 1) You give me a copy of your file and I diagnose and fix it, or 2) I provide a file that I mock up as an example, then you have to compare it to your file to see what's different.

If you don't have a way to share your file I'll do #2 but it will take a little time.

Here is the file shared through OneDrive.

Coastal Trip Calculator.xlsm

Microsoft Excel Workbook

1drv.ms

 

[6StringJazzer]

I found a restriction in my solution that the column it checks must be text. You have protected your file so I cannot see the source data but I will assume that Trip ID is numeric. Therefore the formula should instead refer to Destination Name. The correct formula for Print_Area is

='Contract Invoice'!$A$1:INDEX('Contract Invoice'!$M:$M,10+COUNTIF('Contract Invoice'!$D$11:$D$76, "*?"))

This works in my test file but your file does not have a Print_Area set for sheet Contract Invoice. When I set one then update the formula, the formula does not "stick". Do you have anything your VBA that would interfere with that? Your VBA project is password protected so I can't check.

I also cannot update the data in your file to test it, so you'll have to do that.

 

[6StringJazzer]

One more follow-up: It appears that the reason it won't let me enter the formula is that you have no data to print so it evaluates to an error. When I force it to have data by entering data to override your formulas it works.

Also it appears that data in your file goes down to row 79, not 76 as mentioned in your first post. So formula must be updated accordingly.