VBA Code for Print Area of non contiguous ranges

[AusSteelMan]

Hi everyone,

I would like to request some help with the following problem.

I would like to use some code to set a non contiguous print area using columns A to E and K to AE. The number of rows is determined by finding the last row used.

Here is the code I am up to at the moment:

Sub setprintarea()
Dim LeftRange As String
Dim RightRange As String
Dim LastRow As Integer
 
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    'ActiveWindow.FreezePanes = False
 
    LastRow = ActiveSheet.UsedRange.Rows.Count
    'ActiveSheet.PageSetup.PrintArea = "$A$1:$AE$" & LastRow
 
    LeftRange = Range("a1, e" & LastRow)
    RightRange = Range("k1, ae" & LastRow)
 
    ActiveSheet.PageSetup.PrintArea = LeftRange & RightRange
 
    Application.ScreenUpdating = True
    'ActiveSheet.Protect
End Sub

I get an error at the following line

ActiveSheet.PageSetup.PrintArea = LeftRange & RightRange

When I watched the code execute, the LeftRange was simply the contents of the Cell A1 and the RightRange was the contents of K1
(Which makes sense actually...I think anyway)

Previously I could get A to AE down to last row to work using

ActiveSheet.PageSetup.PrintArea = "$A$1:$AE$" & LastRow

My preference is to be able to set the print area and manually print.

I know I can get the area to set, hide Cols F to J, print out and unhide cols as one code action, but the users want to be able to check before committing the print.

Can anybody offer any advice or help.

Thanks,
ASM

 

[AusSteelMan]

So I have got it to work using this:

Sub setprintarea()
Dim LeftRange As String
Dim RightRange As String
Dim LastRow As Integer
 
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    'ActiveWindow.FreezePanes = False
 
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.PageSetup.PrintArea = "$A$1:$E$" & LastRow & ",$K$1:$AE$" & LastRow
 
    Application.ScreenUpdating = True
    'ActiveSheet.Protect
End Sub

But now I have a new problem.

Cols A to E print on one page (about 1/3 width of A3 Landscape) and then K to AE prints over 2 more pages: the first filled fully and the second about half way.

Can anyone help me to get Col K to print on the first page immediately adj to Col E.

My aim would to be Max 2 pages wide and as many pages long to get the whole sheet.

Thanks,
ASM

 

[AusSteelMan]

OK
I have not been able to get past the automatic page break at the join of the non contiguous ranges with out copying to a new sheet.
(I could actually achieve this by either
1. having a replica worksheet without the columns I don't want printed; or
2. using the "Copy > Paste Picture Link" on a new sheet.)

Since I dont want either of these [messy] options, I decided to use the following code to set the range, hide the columns, print then unhide the columns.

Sub PrintStrategySheet()
Dim LastRow As Integer
 
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    'ActiveWindow.FreezePanes = False
 
 
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AE$" & LastRow
 
    Range("CauseCols").EntireColumn.Hidden = True
 
    'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    MsgBox ("Msg instead of actually printing while testing code")
 
    Range("CauseCols").EntireColumn.Hidden = False
 
 
 
    Application.ScreenUpdating = True
    'ActiveSheet.Protect
End Sub

Big disadvantage is not being able to print preview before committing to print.

This code shows a message box instead of printing so i don't waste paper trying to get it right.

Still trying to make it user friendly.

ASM

 

[AusSteelMan]

So here is a method for getting the print preview and letting the user decide if they want to actually print or not

Sub PrintStrategySheet()
Dim LastRow As Integer
 
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
 
 
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AE$" & LastRow
 
    Range("CauseCols").EntireColumn.Hidden = True
 
    MsgBox ("Preview your print job after clicking OK.  Adjust settings as required, then either Print or Close")
    ActiveWindow.SelectedSheets.PrintPreview
 
    Range("CauseCols").EntireColumn.Hidden = False
 
 
    Application.ScreenUpdating = True
    'ActiveSheet.Protect
End Sub

This way the print preview is enacted, they choose settings as required and whether they print or just close, the code continues and unhides the CauseCols and Ends.

Again, may not be a great coding solution, but I have not had any other offers/suggestions...but I am still open to them please.

Regards,
ASM