Results 1 to 4 of 4

VBA Code for Print Area of non contiguous ranges

This is a discussion on VBA Code for Print Area of non contiguous ranges within the Excel Questions forums, part of the Question Forums category; Hi everyone, I would like to request some help with the following problem. I would like to use some code ...

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Location
    Wollongong, Australia: -34.463949, 150.884085
    Posts
    192

    Default VBA Code for Print Area of non contiguous ranges

    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:

    Code:
     
    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
    Code:
    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
    Code:
     
    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
    Work: Win7, Excel 2010 (as of Sept 2014! thanks big company)
    Home: Win7, Excel 2010

  2. #2
    Board Regular
    Join Date
    Sep 2009
    Location
    Wollongong, Australia: -34.463949, 150.884085
    Posts
    192

    Default Re: VBA Code for Print Area of non contiguous ranges

    So I have got it to work using this:

    Code:
     
    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
    Work: Win7, Excel 2010 (as of Sept 2014! thanks big company)
    Home: Win7, Excel 2010

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Location
    Wollongong, Australia: -34.463949, 150.884085
    Posts
    192

    Default Re: VBA Code for Print Area of non contiguous ranges

    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.

    Code:
    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
    Work: Win7, Excel 2010 (as of Sept 2014! thanks big company)
    Home: Win7, Excel 2010

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Location
    Wollongong, Australia: -34.463949, 150.884085
    Posts
    192

    Default Re: VBA Code for Print Area of non contiguous ranges

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

    Code:
    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
    Work: Win7, Excel 2010 (as of Sept 2014! thanks big company)
    Home: Win7, Excel 2010

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com