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

 

[Joe4]

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”.

For this part, you can do this:

Dim lr as long
Dim rng as Range

'Find last row in column B with data after cell B33
If Range("B34") = "" Then
    lr = 33
Else
    lr = Range("B33").End(xlDown).Row
End If

'Set print range
Set rng = Range("B33:O" & lr)

So then you can use the "rng" as your print range.

 

[EssKayKay]

For this part, you can do this:

Dim lr as long
Dim rng as Range

'Find last row in column B with data after cell B33
If Range("B34") = "" Then
    lr = 33
Else
    lr = Range("B33").End(xlDown).Row
End If

'Set print range
Set rng = Range("B33:O" & lr)

So then you can use the "rng" as your print range.

Joe,

Thank you again for getting back to me so timely. I tried inserting your code into my existing routine. However, I receive a Run-Time error 1004.

As I am quite inept at VBA (amongst many other things), I assume this is because I inserted it incorrectly. I was not sure about using rng as the print range.
Following is my existing routine that is triggered when I click a UserForm button.

Private Sub cmdPrintScheduleALL_Click()
Dim DoIt As Integer
Application.ScreenUpdating = False

DoIt = MsgBox("                          - Print -" & vbCrLf & vbCrLf & "                 PAYMENT SCHEDULE?" & vbCrLf & "    ( Manually select pages to be printed )", vbOKCancel)
    If DoIt = vbOK Then
        UnProtect_It
        Columns("N:O").Hidden = False
        
'========== Code added per Joe at MrExcel ==========
    Dim lr As Long
    Dim rng As Range

'    'Find last row in column B with data after cell B33
    If Range("B34") = "" Then
        lr = 33
    Else
        lr = Range("B33").End(xlDown).Row
    End If

    'Set print range
    Set rng = Range("B33:O" & lr)
'=========================================
              
        
        ActiveSheet.PageSetup.PrintArea = Range("B33:O" & lr)                         '<----- Here's error is happening -----
                                        
        Range("M32").Select

        ActiveSheet.PageSetup.Orientation = xlLandscape
        ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.25)
        ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
        
        Application.Dialogs(xlDialogPrint).Show
        'ExecuteExcel4Macro "PRINT(1,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
        
        
        ActiveSheet.PageSetup.Orientation = xlPortrait
        ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.5)
        ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
        ActiveSheet.PageSetup.PrintArea = ""
        
    End If
Application.ScreenUpdating = True

End Sub

Thanks again

 

[Joe4]

Try:

ActiveSheet.PageSetup.PrintArea = rng

 

[EssKayKay]

I should have noted that I originally used the line:
ActiveSheet.PageSetup.PrintArea = rng

Try:

ActiveSheet.PageSetup.PrintArea = rng

Yes, I did try that but received the same 1004 error. Like I said, it's probably something wrong on my part.

 

[MARK858]

Try...

ActiveSheet.PageSetup.PrintArea = rng.Address

 

[EssKayKay]

Try...

ActiveSheet.PageSetup.PrintArea = rng.Address

Thanks Mark.
I tried adding the line as noted but still received the same 1004 error. Maybe you meant something else by Address. Like I said, I'm not the most ept at VBA.
Any other suggestions would be greatly appreciated.

Private Sub cmdPrintScheduleALL_Click()
Dim DoIt As Integer
Application.ScreenUpdating = False

DoIt = MsgBox("                          - Print -" & vbCrLf & vbCrLf & "                 PAYMENT SCHEDULE?" & vbCrLf & "    ( Manually select pages to be printed )", vbOKCancel)
    If DoIt = vbOK Then
        UnProtect_It
        Columns("N:O").Hidden = False
      
   ActiveSheet.Shapes("MyShape").Visible = False
   ActiveSheet.Shapes("Remarks Line").Visible = False
'----------- Code added per Joe (MrExcel) ------------
    Dim lr As Long
    Dim rng As Range

'    'Find last row in column B with data after cell B33
    If Range("B34") = "" Then
        lr = 33
    Else
        lr = Range("B33").End(xlDown).Row
    End If

    'Set print range
    Set rng = Range("B33:O" & lr)
'--------------------------------------------------------
                      
        ActiveSheet.PageSetup.PrintArea = rng.Address       '<--- added per Mark (MrExcel) ---
                                        
        'Range("M32").Select

        ActiveSheet.PageSetup.Orientation = xlLandscape
        ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.25)
        ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
        
        Application.Dialogs(xlDialogPrint).Show
                
        ActiveSheet.PageSetup.Orientation = xlPortrait
        ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.5)
        ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
        ActiveSheet.PageSetup.PrintArea = ""

    End If

End Sub

 

[EssKayKay]

OOPS Mark - I informed you wrong (my bad). I wish I could edit my previous post. Oh well. . .

I do not receive the 1004 error message (I don't know what I was thinking - apparently I wasn't). Instead, it keeps printing the entire range ("B33:O633"). So I receive numerous blank pages. For testing purposes, I'm only printing to a file.

 

[MARK858]

Try changing

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

to

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

 

[EssKayKay]

I've been playing with this more. Maybe there's another way to attack this. What if I set the print range to "B3:B633". Then "hide" the rows that contain a blank (i.e., "") in range ("B33:B633") before printing; after printing, "unhide" the rows. I tried this manually and it appears to work. Just another idea.