Getting VBA in Excel NOT to print certain ranges where Total is = 0

[ArbeitWali]

Hey Guys/Ladies,

My code currently selects a set number of Named Ranges to print.. It prints everything. Even zero totals..aaargh.

I need help getting VBA code not to print "Ranges" in worksheets where the "Total" any given table is " 0 ".

Sub PrintCDivSum()
 
'error trap added for printing support on 64 and 32 bit OS
On Error Resume Next
 
Dim ws As Worksheet
Dim ws2 As String
Dim DefPrintName As String
 
DefPrintName = Application.ActivePrinter
 
UserForm1.Show
 
If UserForm1.Cancel = "True" Then
    Unload UserForm1
    Exit Sub
End If
 
If UserForm1.OK = "True" Then
    If UserForm1.OptionButton2.Value = True Then
        Application.ActivePrinter = "Adobe PDF on Ne02:"
    Else: End If
End If
 
Unload UserForm1
   
Application.ScreenUpdating = False
 
ws2 = ActiveSheet.Name
 
    With ThisWorkbook
        For Each ws In .Worksheets
            Select Case ws.Name
            Case "GC's"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l1a,l2a").Address
                End With
            Case "DIV 2"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l3a,l4a,l5a,l6a,l7a,l8a,l9a,l10a,l11a,l12a,l13a").Address
                End With
            Case "DIV 3"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l14a,l15a,l16a,l17a,l18a").Address
                End With
            Case "DIV 4"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l19A").Address
                End With
            Case "DIV 5"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l20a,l21a").Address
                End With
            Case "DIV 6"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l22a,l23a").Address
                End With
            Case "DIV 7"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l24a,l25a,l26a,l27a,l28a").Address
                End With
            Case "DIV 8"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l29a,l30a").Address
                End With
            Case "DIV 9"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l31a,l32a,l33a,l34a,l35a,l36a").Address
                End With
            Case "DIV 10"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l37a,l38a").Address
                End With
            Case "DIV 14"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                   .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l39A").Address
                End With
            Case "DIV 15"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l40A,l41a,l42a").Address
                End With
            Case "DIV 16"
                With ws
                    .PageSetup.PrintArea = "" 'clear any existing print area
                    .PageSetup.Orientation = xlPortrait
                    .PageSetup.PaperSize = xlPaperLetter
                    .PageSetup.Zoom = False
                    .PageSetup.FitToPagesWide = 1
                    .PageSetup.FitToPagesTall = 1
                    .PageSetup.PrintArea = .Range("l43A").Address
                End With
                'Case Else
            End Select
        Next ws
    End With
    
Sheets(Array("GC's", "DIV 2", "DIV 3", "DIV 4", "DIV 5", "DIV 6", "DIV 7", "DIV 8", "DIV 9", "DIV 10", "DIV 14", "DIV 15", "DIV 16")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
 
'ActiveWindow.SelectedSheets.PrintPreview
 
Application.ActivePrinter = DefPrintName
Application.ScreenUpdating = True
Sheets(ws2).Select
 
End Sub

 

[Joe4]

Re: Gedtting VBA in Excel NOT to print certain ranges where Total is = 0

Duplicate: http://www.mrexcel.com/forum/excel-...rksheets-where-total-any-given-table-0-a.html

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).