Avatar of desmondwkng
desmondwkng
Flag 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
Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
desmondwkng

8/22/2022 - Mon
Anthony Berenguel

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

desmondwkng

ASKER
Hi adbea

How do I define "rngtotal" in the VBA

Desmond
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Anthony Berenguel

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
byundt

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

desmondwkng

ASKER
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
byundt

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
desmondwkng

ASKER
thanks to all