Setting print parameters based on worksheet criterium

Bill
If I've undersood correctly what you need, you can create the macro yourself with the macro recorder. So instead of just providing you with the code, I'll run you through it.

If you wanted to do what you've requested without a macro, you would merely hide the rows that are blank in Column A, set your print area, print, clear the print area, and then unhide the rows.

OK, let's record that in a macro :
- Turn on the macro recorder
- Select Column A
- Go to Edit>GoTo>Special>Blanks and click OK
- Go to Format>Row>Hide
- Select your range of data (let's say it's A1:J10)
- Go to File>PrintArea>SetPrintArea
- Print
- Go to File>PrintArea>ClearPrintArea
- Go to Format>Row>UnHide
- Turn off the macro recorder

Go to the code module containing the macro you've just recorded (Alt+F11).
You should have two lines of code in the macro that set the print area and look like this :-

Range("A1:J10").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$10"

Replace these two lines with your code for setting the print area :-
Worksheets("Box Information").PageSetup.PrintArea = "a1:J" & Worksheets("Box Information").Range("a6000").End(xlUp).Row

To do the same for Column L(so as to print Columns L-U ?), add to the macro the same code and just change the column refs.

To clarify ....

I said "To do the same for Column L(so as to print Columns L-U ?), add to the macro the same code and just change the column refs."

This means repeat ALL of the code at the end of the macro with revised Column refs.

Re: To clarify ....

Hm. There are a couple of inherent problems with your suggestion of using a macro: 1. This worksheet I am working on will be an electronic form used by a large company... over an intranet. This company has a general ban on macro's for the sake of protecting against macro viruses.

Secondly, I think you are misunderstanding the requirement I have in mind. The idea here is that the data will be entered row after row, each row representing all the information for a specific item. This being the case, the users will be filling in every row, but I want to limit the printing to only print the pages where information has been entered in the rows. The problem I currently have is that while the worksheet is displaying blank cells, most of them have some sort of data validation, or formula in them. Excel sees this as data entered in the cell and therefore considers this something to be printed.

I want the printed out sheets to be limited to what has been entered by the users. I need a-j to be page 1, k-r to be page 2. Then back to the bottom of whatever rows fit onto page 1 in a-j to start page three, then the same for k-r and so on.

BUT each page is to only print out IF something has been entered that far down in the worksheet.

It would be easiest for me to just have a line or two of code to insert in the "before print" event.

Can you help? Is this even possible?

Regards,

Bill.

Re: To clarify ....

Secondly, I think you are misunderstanding the requirement I have in mind. The idea here is that the data will be entered row after row, each row representing all the information for a specific item. This being the case, the users will be filling in every row, but I want to limit the printing to only print the pages where information has been entered in the rows. The problem I currently have is that while the worksheet is displaying blank cells, most of them have some sort of data validation, or formula in them. Excel sees this as data entered in the cell and therefore considers this something to be printed. I want the printed out sheets to be limited to what has been entered by the users. I need a-j to be page 1, k-r to be page 2. Then back to the bottom of whatever rows fit onto page 1 in a-j to start page three, then the same for k-r and so on. BUT each page is to only print out IF something has been entered that far down in the worksheet. It would be easiest for me to just have a line or two of code to insert in the "before print" event. Can you help? Is this even possible? Regards,

If your company has a general ban on macros, how can you "just have a line or two of code to insert in the "before print" event" ?

You only have two options. Either you manually set the required print area, or you use VBA.

One question :- Do you mean that rows with cells displaying no data only appear below the last row that does display data ?

If you want the VBA code to do it, post again. Alternatively, if the answer to my question is "yes", then you can record the following with the macro recorder and put the resulting code in a before-print procedure :-

- Select column A
- Insert>Columns
- Type =IF(B1="","",1) and press Ctrl+Enter
- Edit>GoTo>Special>Formulas>Text
- Format>Row>Hide
- Format>Column>Hide
- File>PageSetUp>Sheet>PageOrder>OverThenDown
- Print
- Format>Row>UnHide
- Edit>Delete>EntireColumn

If you want, after you've recorded it, I'll tidy up the code to make it more efficient .