Link to home
Start Free TrialLog in
Avatar of desmondwkng
desmondwkngFlag for Hong Kong

asked on

Excel VBA

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
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

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

Avatar of desmondwkng

ASKER

Hi adbea

How do I define "rngtotal" in the VBA

Desmond
Avatar of byundt
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

SOLUTION
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks to all