Announcement

Collapse
No announcement yet.

Print Non Contiguous Ranges

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Print Non Contiguous Ranges



    Hi all -

    I would like to print a report with noncontiguous columns side by side as defined in this range
    Code:
    With wsMarket
            Set rngPrint = Union(.Range("D3:D" & lngRows), .Range("O3:O" & lngRows), _
                .Range("Z3:Z" & lngRows), .Range("AK3:AK" & lngRows), .Range("AV3:AV" & lngRows), _
                .Range("BG3:BG" & lngRows), .Range("BR3:BR" & lngRows), .Range("CC3:CC" & lngRows), _
                .Range("CN3:CN" & lngRows), .Range("CY3:CY" & lngRows), .Range("DJ3:DJ" & lngRows), _
                .Range("DU3:DU" & lngRows), .Range("EF3:EF" & lngRows), .Range("EQ3:EQ" & lngRows), _
                .Range("FB3:FB" & lngRows), .Range("FM3:FM" & lngRows), .Range("FX3:FX" & lngRows))
        End With
    The page setup is defined as landscape 1 page wide as many pages tall as need to print 256 rows. But in my first test this report produced 153 pages. I would like 3-4 max. 2 would be better.

    Full code below
    TIA
    -marc

    Code:
    Sub PrintRates()
    
        Dim wb As Workbook
        Dim wsMarket As Worksheet
        Dim rngPrint As Range
        Dim lngRows As Long
        
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .DisplayAlerts = False
        End With
        
        Set wb = ThisWorkbook
        Set wsMarket = wb.Worksheets("Market")
        lngRows = 256
        With wsMarket
            Set rngPrint = Union(.Range("D3:D" & lngRows), .Range("O3:O" & lngRows), _
                .Range("Z3:Z" & lngRows), .Range("AK3:AK" & lngRows), .Range("AV3:AV" & lngRows), _
                .Range("BG3:BG" & lngRows), .Range("BR3:BR" & lngRows), .Range("CC3:CC" & lngRows), _
                .Range("CN3:CN" & lngRows), .Range("CY3:CY" & lngRows), .Range("DJ3:DJ" & lngRows), _
                .Range("DU3:DU" & lngRows), .Range("EF3:EF" & lngRows), .Range("EQ3:EQ" & lngRows), _
                .Range("FB3:FB" & lngRows), .Range("FM3:FM" & lngRows), .Range("FX3:FX" & lngRows))
        End With
        ShowAll
        BeforePrint
        
        '=========================================================================
        '/Print Process
            With Sheets("Market")
                .PageSetup.PrintArea = rngPrint.Address
            End With
            
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            
        '/End Print Process
        '=========================================================================
    
        AfterPrint
        wsMarket.Range("A1").Select
        
        Set wb = Nothing
        Set wsMarket = Nothing
        Set rngPrint = Nothing
        
        With Application
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
            .DisplayAlerts = True
        End With
        
    End Sub
    ShowAll
    Code:
    Sub ShowAll()
    Dim wb As Workbook
    Dim shtMarket As Worksheet
    Dim rngAurora As Range
    Dim rngFull As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb = ThisWorkbook
    Set shtMarket = wb.Worksheets("Market")
    Set rngFull = shtMarket.Columns("D:IV")
    rngFull.EntireColumn.Hidden = False
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    
    End Sub
    Before Print
    Code:
    Private Sub BeforePrint()
        Dim wb As Workbook
        Dim wsMarket As Worksheet
        Dim rngHeader As Range
        
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        End With
        
        Set wb = ThisWorkbook
        Set wsMarket = wb.Worksheets("Market")
        Set rngHeader = wsMarket.Range("A1:FH1")
        
        With rngHeader
            .Interior.ColorIndex = 1
            .Font.ColorIndex = 39
        End With
        
        With Application
            .DisplayAlerts = True
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End With
        
        Set wb = Nothing
        Set wsMarket = Nothing
        Set rngHeader = Nothing
        
        '    Range("A1:FH1").Select
        '    Selection.Interior.ColorIndex = 1
        '    Selection.Font.ColorIndex = 39
        '    Range("A1").Select
    End Sub
    After Print
    Code:
    Private Sub AfterPrint()
    
        Dim wb As Workbook
        Dim wsMarket As Worksheet
        Dim rngHeader As Range
        
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        End With
        
        Set wb = ThisWorkbook
        Set wsMarket = wb.Worksheets("Market")
        Set rngHeader = wsMarket.Range("A1:FH1")
        
        With rngHeader
            .Interior.ColorIndex = 36
            .Font.ColorIndex = 1
        End With
        
        With Application
            .DisplayAlerts = True
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End With
        
        Set wb = Nothing
        Set wsMarket = Nothing
        Set rngHeader = Nothing
    
        '    Range("A1").Select
        '    Range(Selection, Selection.End(xlToRight)).Select
        '    Selection.Interior.ColorIndex = 36
        '    Selection.Font.ColorIndex = 1
        '    Range("A1").Select
    End Sub

  • #2
    Re: Print Noncontiguous Columns In One Report

    The page setup is defined as landscape 1 page wide as many pages tall as need to print 256 rows. But in my first test this report produced 153 pages. I would like 3-4 max. 2 would be better.
    How can anyone scale down a 153 page report down to 2-3 pages? Unless you reduce the zoom to some silly setting.

    Comment


    • #3
      Re: Print Non Contiguous Ranges

      Thanks Dave
      Fortunately I wasn't connected to the printer when I first ran the macro
      The 153 pages is an error
      The expected output should be 4 pages at most

      Thanks
      -marc

      Comment


      • #4
        Re: Print Non Contiguous Ranges

        What does print preview show you?

        Comment


        • #5


          Re: Print Non Contiguous Ranges

          Thanks Dave -

          Sorry for the delay
          Just back from well deserved vacation

          I ended up with work-around
          I setup preformatted workbook
          Copy the desired ranges from the data source to the final report

          A long way from where I started, but works well for me

          Thanks!
          -marc

          Comment

          Working...
          X