Excel VBA

desmondwkng
desmondwkng used Ask the Experts™
on
Hi experts,

I've an VBA to print out reports for for different catergories, but sometime there is nothing in the cat and therefore I don't need to print it out.
That is the "Blue" cell (G61) in the attached file, but the column "G" is definite but row "61" is a variable which depends on the number of entries of that date.
In short, if G61(or the Blue cell)=0, no need to print and go to next

Can you help me to modify my macro,

    Application.ScreenUpdating = False
    Selection.AutoFilter Field:=7, Criteria1:="Cash"
    ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    Selection.AutoFilter Field:=7, Criteria1:="Cheque"
    ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    Selection.AutoFilter Field:=7, Criteria1:="Credit Card"
    ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    Selection.AutoFilter Field:=7, Criteria1:="Direct(HSBC)"
    ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    Selection.AutoFilter Field:=7
    ActiveWorkbook.Save


Thanks

Desmond
Collection-Summary-1205--.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this...

Sub Printout()
'
' Printout Macro
' Macro recorded 20/03/2012 by Desmond Ng
'

'
    
    Application.ScreenUpdating = False
    Selection.AutoFilter Field:=7, Criteria1:="Cash"
    If Range("rngtotal").Value <> 0 Then
        ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    End If
    
    Selection.AutoFilter Field:=7, Criteria1:="Cheque"
    If Range("rngtotal").Value <> 0 Then
        ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    End If

    Selection.AutoFilter Field:=7, Criteria1:="Credit Card"
    If Range("rngtotal").Value <> 0 Then
        ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    End If
    
    Selection.AutoFilter Field:=7, Criteria1:="Direct(HSBC)"
    If Range("rngtotal").Value <> 0 Then
        ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
    End If
    
    Selection.AutoFilter Field:=7
    ActiveWorkbook.Save
End Sub

Open in new window

Author

Commented:
Hi adbea

How do I define "rngtotal" in the VBA

Desmond
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Another approach is to count the number of visible rows and areas after applying the filter. If 3 or more, then the macro should print.

Sub Printout()
'
' Printout Macro
' Macro recorded 20/03/2012 by Desmond Ng

    Dim fil As AutoFilter
    Dim rg As Range
    Set fil = ActiveSheet.AutoFilter
    Set rg = fil.Range
    Application.ScreenUpdating = False
    rg.Rows(1).AutoFilter field:=7, Criteria1:="=Cash"
    MsgBox rg.SpecialCells(xlCellTypeVisible).Areas.Count
    If (rg.SpecialCells(xlCellTypeVisible).Rows.Count + rg.SpecialCells(xlCellTypeVisible).Areas.Count) > 3 Then ActiveSheet.Printout
    rg.Rows(1).AutoFilter field:=7, Criteria1:="=Cheque"
    If (rg.SpecialCells(xlCellTypeVisible).Rows.Count + rg.SpecialCells(xlCellTypeVisible).Areas.Count) > 3 Then ActiveSheet.Printout
    rg.Rows(1).AutoFilter field:=7, Criteria1:="Credit Card"
    If (rg.SpecialCells(xlCellTypeVisible).Rows.Count + rg.SpecialCells(xlCellTypeVisible).Areas.Count) > 3 Then ActiveSheet.Printout
    rg.Rows(1).AutoFilter field:=7, Criteria1:="Direct(HSBC)"
    If (rg.SpecialCells(xlCellTypeVisible).Rows.Count + rg.SpecialCells(xlCellTypeVisible).Areas.Count) > 3 Then ActiveSheet.Printout
    rg.Rows(1).AutoFilter field:=7
    ActiveWorkbook.Save
End Sub

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Oh yeah! Sorry, Desmond.

To define the rngTotal named range in excel:
1. Set the focus on cell I61
2. Go to the name box and enter "rngTotal"

For information about the excel name box visit http://spreadsheets.about.com/od/glossary/g/name_box_def.htm

Of course instead of taking the named range approach, you could simply replace "rngTotal" with "I61" in the VBA code.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Assuming that the blue cell is always in column I and two rows below your last data in column A, you could simplify the code to:

Sub Printout()
'
' Printout Macro
' Macro recorded 20/03/2012 by Desmond Ng

    Dim fil As AutoFilter
    Dim rg As Range, rgSubtotal As Range
    Dim v As Variant
    Set fil = ActiveSheet.AutoFilter
    Set rg = fil.Range
    Set rgSubtotal = Cells(Rows.Count, 1).End(xlUp)         'Last data cell in column A
    Set rgSubtotal = rgSubtotal.EntireRow.Cells(3, "I")     'Actual cell containing SUBTOTAL formula
    Application.ScreenUpdating = False
    For Each v In Array("=Cash", "=Cheque", "=Credit Card", "=Direct(HSBC)")
        rg.Rows(1).AutoFilter field:=7, Criteria1:=v
        If rgSubtotal.Value <> 0 Then ActiveSheet.Printout
    Next
    rg.Rows(1).AutoFilter field:=7
    ActiveWorkbook.Save
End Sub

Open in new window

Author

Commented:
Dear Byundt

Noted.
But sometimes, there will be not cheque collection or direct payment or credit card payment, the current VBA will still print out a blank page.
May I ask the meaning of "3" on the Areas.Count.

regards,

Desmond
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
In my first sub, I was testing the number of visible rows in the filter range plus the number of non-contiguous areas. I found that for Cash (no visible rows), there were one row visible (the header row) in the first area, and 2 total areas. In all other cases, there were at least four visible rows plus areas. So the code as written will not print anything for Cash, but will print for Cheque, Credit Card and Direct(HSBC).

In my second sub, I tried to locate the blue cell in column I, then tested it to see whether to print or not. If you need to add more filter possibilities, just put them in the Array statement. The looping will take care of the rest.

Both approaches worked in your sample workbook, but I think the second is easier to modify and maintain.

Brad
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Eliminating some unused code, the second sub simplifies further to:
Sub Printout()
'
' Printout Macro
' Macro recorded 20/03/2012 by Desmond Ng

    Dim rg As Range, rgSubtotal As Range
    Dim v As Variant
    Set rg = ActiveSheet.AutoFilter.Range.Rows(1)
    Set rgSubtotal = Cells(Rows.Count, 1).End(xlUp).Range("I3")    'Cell containing SUBTOTAL formula
    Application.ScreenUpdating = False
    For Each v In Array("=Cash", "=Cheque", "=Credit Card", "=Direct(HSBC)")
        rg.AutoFilter field:=7, Criteria1:=v
        If rgSubtotal.Value <> 0 Then ActiveSheet.PrintPreview
    Next
    rg.AutoFilter field:=7
    ActiveWorkbook.Save
End Sub

Open in new window

Author

Commented:
thanks to all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial