This is not my code, as somebody helped me on stackflow. However I am still stuck on it so posting it again.

Essentially the code counts blank cells in column E, so the logic is working. However, before counting blank cells I want to exclude "Cash" from column H.

Below is the code, it does apply the filter on column H (excludes cash), however the blank cells are counted for the data including cash.

Sub exampleTHis()

    ActiveSheet.Range("H:H").AutoFilter Field:=8, Criteria1:="<>Cash", _
    Operator:=xlAnd

Dim ws As Worksheet, testRange As Range, aCount As Long, zAnswer

For Each ws In ThisWorkbook.Worksheets

Set testRange = Intersect(ws.Range("E:E"), ws.UsedRange)
 'Set testRange = ws.Range("E2", Range("E" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
 
    If Not testRange Is Nothing Then
        
        aCount = Application.WorksheetFunction.CountBlank(testRange)
        If aCount > 0 Then
         'blank cells found....
            zAnswer = MsgBox(aCount & " blank values found in at " & ws.Name & testRange.Address & ".  Continue macro?", vbYesNo)
            
            If zAnswer = vbNo Then Exit For
            
        End If
    End If
    
Next ws

End Sub

CodePudding user response：

Count Blanks in Auto-Filtered Column Using AutoFilter and SpecialCells

• The complications are necessary to be able to write the address of the critical cells in the message box. You can simplify if you're just after the count.

Sub CountBlanksInFilteredColumn()
    ' Not blank:           "<>"
    ' Blank:               "" or "=" (includes Empty)
    ' Blank but not empty: "<=>"
    ' Empty?
    
    Const SHEET_NAME As String = "Assets"
    Const BLANK_COLUMN As Long = 5
    Const BLANK_CRITERION As String = ""
    Const CASH_COLUMN As Long = 8
    Const CASH_CRITERION As String = "<>Cash"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets(SHEET_NAME)
    
    Dim vrg As Range
    
    With ws
        If .FilterMode Then .ShowAllData ' clear filters
        Dim rg As Range: Set rg = ws.UsedRange
        Dim crg As Range ' without header
        With rg
            Set crg = .Columns(BLANK_COLUMN).Resize(.Rows.Count - 1).Offset(1)
            .AutoFilter BLANK_COLUMN, BLANK_CRITERION
            .AutoFilter CASH_COLUMN, CASH_CRITERION
        End With
        On Error Resume Next
            Set vrg = crg.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        .AutoFilterMode = False ' turn off auto filter
    End With
    
    Dim Blanks As Long, vrgAddress As String
    
    If Not vrg Is Nothing _
        Then Blanks = vrg.Cells.Count: vrgAddress = vrg.Address(0, 0)
    
    MsgBox "Found " & Blanks & " blank cell" _
        & IIf(Blanks = 1, "", "s") _
        & IIf(Blanks = 0, ".", ":" & vbLf & vrgAddress), _
        IIf(Blanks = 0, vbExclamation, vbInformation)
    
End Sub

Continue If No Blank Cells

    End With
    
    If Not vrg Is Nothing Then
        MsgBox "Found " & vrg.Cells.Count & " blank cell" _
            & IIf(Blanks = 1, "", "s") & ":" & vbLf & vrg.Address(0, 0), _
            vbExclamation
        Exit Sub
    End If
    
    ' No blanks found, continue with your code.

End Sub