Print routines

[EssKayKay]

I’m trying to create a couple print routines. Maybe I should post these separately.

1. Set a print range based on values in a range per length of a loan term. The print range would vary between B33:O33 to B33:O633. Row 33 will always contain data. The following rows will vary depending upon the length of the loan with the max being Row 633 (“B33:O633”).

All of the cells in “B33:B633” contain formulas. Here’s an example of one of the shorter formulas à =IF(B50="","",start_rate)

Again, depending on the length of the loan, many rows/cells will be blank (i.e., “”). So what I was trying to do was set the print range beginning with B33:O33 and continuing down the range until I find a cell (say in column B) that contains a blank (i.e., “”). Then set the print range to one row up from there. That is, let’s say cell B300 has a value in it and B301 is blank. Then set the print range to “B33:O300”.


2. Is there a way to bring up the Print Dialog box already set to:
- Pages (not All)
- From 1,
- To 1
Then pause there waiting for the user to Press OK. This way the default will be only printing the first page. If the user wants to print more pages, they would have to change the From/To values accordingly and then press OK. I tried a couple commands but no luck:
- Application.Dialogs(xlDialogPrint).Show
- ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"

Steve

 

[MARK858]

Did you try the suggestion in post 9?

 

[EssKayKay]

Try changing

lr = Range("B33").End(xlDown).Row

to

lr = Columns(2).Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row

I think you got it. I'll have to play with this more tomorrow but it looks good. Right now I'm having an issue with headers, footers, and margins but I'll work on that.

Thank you very much Mark and Joe for your time and consideration with this.

My next issue which may not be possible is to see if there is a way to have the Print Dialog box to default to "Pages", "From 1" and "To 1". I'm not sure this is apropoe but I may repost just this issue.

Thank you once again. You guys truly are amazing.
Steve

 

[EssKayKay]

Did you try the suggestion in post 9?

I did. We must have been posting at the same time. I think you got it Mark and I thank you (see above post). I'm off to bed now. I'll look at this closer tomorrow.

Once again, sincerely appreciated,
Steve

 

[MARK858]

You're welcome, happy we could help