Set Print Area Based On A Condition

  • I have been searching without real success for a soloution to a print area problem. Hopefully this one us easy for those blessed with the more ability than me.
    I have a worksheet named "TQUOTE" with the print area defined as A1:E:286 resulting in 5 pages printing ,however if there are only 3 pages of data I will be left with 2 blank pages being printed. ( along with the header rows which are set to repeat for each page ). I should mention this worksheet is included in a list of sheets to print based on the response to a user form.
    Would it be possible to have the print area change based on the an empty cell that related to the next page break? eg. If the page break for page 1 ends at row 55, if a cell in row 56 were empty then set the print area to be A1:E55
    If the page break for page 2 ends at row 85, if a cell in row 86 were empty then set the print area to be A1:E85 and so on ??
    I do not have the skills yet to write this macro , and i bow to thiose that do !<smirk>
    any and all help would be greatly appreciated.
    Jeff

  • Re: Set Print Area Based On A Condition



  • Re: Set Print Area Based On A Condition


    Hi Mrk and thank you for your response. I tried editing your code to adjust my print area based on specified cells being empty. For some reason the print area remains at A18 : E286 even when all specified cells are empty. This is how i modified your code. Also does it matter that I have the page set up to have several rows repeat at the top of each page?


    Your help is greatly appreciated
    Jeff

  • Re: Set Print Area Based On A Condition


    Hi Jeff,


    I tried it, and it worked for me when I populated different areas of the A column.


    Are you sure that the cells of your TQUOTE worksheet are completely empty--no spaces, 0s, or hidden formulas? You can do a quick check (though a space might not show up) with a message box:


    Code
    msgbox Worksheets("TQUOTE").Range("A254").Value


    You can also go into the VBE (ALT-F11), highlight the values, select Debug from the Menu, and Quick Watch to find out the values that the variable takes. Then, with your cursor inside the macro, hit the key F8 repeatedly to see the effect on the variable(s) by advancing one line at a time.


    I don't think it should matter about the repeating rows.


    The only change I'd recommend is perhaps moving the print-statement, since you probably don't want it to send a print-job if the print-area is set to empty.


    Mark



    Quote from jandrew

    Hi Mrk and thank you for your response. I tried editing your code to adjust my print area based on specified cells being empty. For some reason the print area remains at A18 : E286 even when all specified cells are empty. This is how i modified your code. Also does it matter that I have the page set up to have several rows repeat at the top of each page?
    Jeff

  • Re: Set Print Area Based On A Condition


    Hi Mark,
    Turns out the cells on "TQUOTE" sheet did have hidden formulas. I edited the code to look at the cells on another sheet "TENTRY" which are linked to the cells on "TQUOTE ". I also removed the lines for print out etc. as I want to bypass the print dialogue . The macro works well .
    You may not realize how difficult all of this can be for a beginner like me, which makes your help invaluable and greatly appreciated.
    Thank-you very much for the assistance.
    Here is the adjusted code.

  • Re: Set Print Area Based On A Condition


    Hi Mark,
    After more testing I realize that if I step through the macro it it will stop at the line of code which is true, and then and only then will the macro update the print area. I don't understand why it does not update whem there is a change in one of the cells on the "TENTRY" worksheet.
    I guess I jumped the gun saying it was working well. My apologies.
    Jeff

  • Re: Set Print Area Based On A Condition


    Instead of using [COLOR="Red"]<> ""[/COLOR] in the macro, try using [COLOR="red"]> 0[/COLOR] and see if that works.


    To avoid the message when printing, you might try inserting this in the code:

    Code
    ' first line of the macro:
    application.displayalerts = false
    
    
    ' last line of code:
    application.displayalerts = true

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!