Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    VB:
    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

    VB:
    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
    VB:
    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
    VB:
    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
    VB:
    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,713

    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,713

  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, 11:03
  2. Clear Non-Contiguous Ranges
    By kokoss in forum EXCEL HELP
    Replies: 7
    Last Post: February 28th, 2008, 00:03
  3. Count #N/A in Non-contiguous Ranges
    By coolhandphil in forum EXCEL HELP
    Replies: 8
    Last Post: September 13th, 2007, 15:12
  4. Compare Non-Contiguous Ranges
    By Alu in forum EXCEL HELP
    Replies: 11
    Last Post: June 21st, 2007, 22:38
  5. Autofill Non-Contiguous Ranges
    By Syntinal in forum EXCEL HELP
    Replies: 1
    Last Post: November 22nd, 2006, 13: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