Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Print Non Contiguous Ranges

  1. #1
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

    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.

  3. #3
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

  5. #5
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Sort Non-Contiguous Ranges
    By heizeer in forum EXCEL HELP
    Replies: 1
    Last Post: April 8th, 2008, 12:03
  2. Clear Non-Contiguous Ranges
    By kokoss in forum EXCEL HELP
    Replies: 7
    Last Post: February 28th, 2008, 01:03
  3. Count #N/A in Non-contiguous Ranges
    By coolhandphil in forum EXCEL HELP
    Replies: 8
    Last Post: September 13th, 2007, 16:12
  4. Compare Non-Contiguous Ranges
    By Alu in forum EXCEL HELP
    Replies: 11
    Last Post: June 21st, 2007, 23:38
  5. Autofill Non-Contiguous Ranges
    By Syntinal in forum EXCEL HELP
    Replies: 1
    Last Post: November 22nd, 2006, 14:41

Bookmarks

Posting Permissions

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