VBA Print Preview only displaying 1 page

[Jasesair]

Having a little trouble with the below code. It's only displaying one page when more data exists for a second print preview page.

Sub PrintJuniors2021()
Dim LastRow As Long
Application.ScreenUpdating = False
Columns("AN:BQ").EntireColumn.Hidden = False
LastRow = Sheets("2021").Range("AO" & Rows.Count).End(xlUp).Row
Sheets("2021").PageSetup.PrintArea = "AO2:AT107" & LastRow
With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    End With
ActiveSheet.PrintPreview
Columns("AN:BQ").EntireColumn.Hidden = True
End Sub

 

[Jasesair]

It's not showing anything. I have to click on 'show print preview' but then it's just blank pages. No change removing the .Zoom line.

 

[MARK858]

Any difference?

Sub PrintJuniors2021()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Sheets("2021").Columns("AN:BQ").Hidden = False
    LastRow = Sheets("2021").Range("AO" & Rows.Count).End(xlUp).Row
   
    Sheets("2021").PageSetup.PrintArea = ""
    Application.PrintCommunication = False
   
    With Sheets("2021").PageSetup
        .Orientation = xlLandscape
        .PrintArea = Sheets("2021").Range("AO2:AT" & LastRow).Address
    End With
    Application.PrintCommunication = True
    Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
 
    'Sheets("2021").Columns("AN:BQ").Hidden = True
End Sub

 

[Jasesair]

That works! It's still forcing me to click on 'Show Print Preview', and it isn't doing the last row at all - just printing all rows.

When I try to allow the hiding of the columns again, I'm back to getting blank sheets.

This is where I'm at now. Couple of adjustments to columns etc.

Sub PrintJuniors2021()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Sheets("2021").Unprotect Password:=""
    Sheets("2021").Columns("Aq:av").Hidden = False
    LastRow = Sheets("2021").Range("Ar" & Rows.Count).End(xlUp).Row
   
    Sheets("2021").PageSetup.PrintArea = ""
    Application.PrintCommunication = False
   
    With Sheets("2021").PageSetup
        .Orientation = xlLandscape
        .PrintArea = Sheets("2021").Range("Aq1:Av" & LastRow).Address
    End With
    Application.PrintCommunication = True
    Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
'    Sheets("2021").Columns("Aq:av").Hidden = True
    Sheets("2021").Protect Password:=""

End Sub

 

[MARK858]

and it isn't doing the last row at all - just printing all rows

It is finding the Last row fine for me, what is in showing as the range for Print_Area under name manager once the macro has run?

It's still forcing me to click on 'Show Print Preview',

It doesn't for me in 365, it brings up

 

[MARK858]

What I do notice is the 2021 sheet has to be active when the code runs.

Sub PrintJuniors2021()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Sheets("2021").Columns("AN:BQ").Hidden = False
    LastRow = Sheets("2021").Range("AO" & Rows.Count).End(xlUp).Row
   
    Sheets("2021").PageSetup.PrintArea = ""
    Application.PrintCommunication = False
   
    With Sheets("2021").PageSetup
        .Orientation = xlLandscape
        .PrintArea = Sheets("2021").Range("AO2:AT" & LastRow).Address
    End With
    Application.PrintCommunication = True
    Sheets("2021").Activate
    Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")

    'Sheets("2021").Columns("AN:BQ").Hidden = True
End Sub

Are you sure that you have nothing below your data in column AO? including formulas returning "".

 

[Jasesair]

The Name Manager is giving the area as $AQ$1:$AV$106

 

[MARK858]

See my edit in bold in my last post.

 

[Jasesair]

Ah yes, that's the issue! I've got an iferror returning ""... This is for all cells in the range.

 

[MARK858]

Sub PrintJuniors2021()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Sheets("2021").Columns("AN:BQ").Hidden = False
    LastRow = Sheets("2021").Columns("AO").Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Sheets("2021").PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    
    With Sheets("2021").PageSetup
        .Orientation = xlLandscape
        .PrintArea = Sheets("2021").Range("AO2:AT" & LastRow).Address
    End With
    Application.PrintCommunication = True
    Sheets("2021").Activate
    Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
 
    'Sheets("2021").Columns("AN:BQ").Hidden = True
End Sub

 

[Jasesair]

That's it! Is there any way for force the Show Print Preview? And, I'd really like to be able to automatically hide the columns after the macro has run?

This is the latest:

Sub PrintJuniors2021()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Sheets("2021").Unprotect Password:=""
    Sheets("2021").Columns("Aq:av").Hidden = False
    LastRow = Sheets("2021").Columns("AR").Find("*", , xlValues, , xlByRows, xlPrevious).Row
   
    Sheets("2021").PageSetup.PrintArea = ""
    Application.PrintCommunication = False
   
    With Sheets("2021").PageSetup
        .Orientation = xlLandscape
        .PrintArea = Sheets("2021").Range("Aq1:Av" & LastRow).Address
    End With
    Application.PrintCommunication = True
    Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
'    Sheets("2021").Columns("Aq:av").Hidden = True
    Sheets("2021").Protect Password:=""
End Sub