Using CountIfs in VBA with filtered data

user_19877712 0
Viewed 52

Firstly, I have filtered out some of my data using the Autofilter function. As a result, the filtered data consists of a non-contiguous range of cells. Consequently, for example, when I want to use the CountIfs function in order to count the number of 03-In Analysis from Column C that belong to 07-customer noticed from column A, the CountIfs function still counts the unfiltered data as the output.

Filtered Data

Filtered Data

When I use SpecialCells(xlCellTypeVisible), I end up getting an error due to the non-contiguous range of cells.

Since, I am a beginner in VBA and coding in general, I am unable to figure out an appropriate solution for this problem. Therefore any help would be really appreciated!

Here's the code I've written so far:

Dim sh, ws As Worksheet
Dim count
Dim range1, range2 As Range

Set range1 = ws.Range("A2:A297")
Set range2 = ws.Range("C2:C297")

count = WorksheetFunction.CountIfs(range1, "07-customer noticed", range2, "03-In Analysis")
sh.Range("A1") = count
vba excel
1 Answers

Arrays work faster for me than worksheet functions.

I tried and tested the code below and it works for me.


    Option Explicit
    
    Private Sub Test()
    
    Dim sRange$
    Dim count&, iLastUsedRow&, iRow&
    Dim aData As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("B")
    
    With ws
        'last used row of data on this sheet
        iLastUsedRow = .Range("A" & Rows.count - 1).End(xlUp).Row
        
        'cells containing data
        sRange = "A2:C" & iLastUsedRow
        'transferring data to array
        aData = .Range(sRange)
    End With
    
    For iRow = 1 To UBound(aData)
        If Range_IsVisibleInWindow(ws.Range("A" & iRow + 1)) Then
            If aData(iRow, 1) = "07-customer noticed" And aData(iRow, 3) = "03-In Analysis" Then
                count = count + 1
            End If
        End If
    Next
        
    End Sub

I copied this function from here and upvoted their answer. You may want to thank them too in this way, if this works for you?


    Function Range_IsVisibleInWindow(ByVal target As Excel.Range) As Boolean
    ' Returns TRUE if any cell in TARGET (Range) is visible in the Excel window.
    '
    '   Visible means (1) not hidden, (2) does not have row height or column width of
    '   zero, (3) the view is scrolled so that the Range can be seen by the user at
    '   that moment.
    '
    '   A partially visible cell will also return TRUE.
    
        If target Is Nothing Then
            ' Parameter is invalid.  Raise error.
            Err.Raise 3672, _
                      "Range_IsVisibleInWindow()", _
                      "Invalid parameter in procedure 'Range_IsVisible'."
    
        Else
            ' Parameter is valid.  Check if the Range is visible.
            Dim visibleWinLarge As Excel.Range
            Dim visibleWinActual As Excel.Range
    
            On Error Resume Next
            Set visibleWinLarge = Excel.ActiveWindow.VisibleRange ' active window range -INCLUDING- areas with zero column width/height
            Set visibleWinActual = visibleWinLarge.SpecialCells(xlCellTypeVisible) ' active window range -EXCLUDING- areas with zero column width/height
            Range_IsVisibleInWindow = Not Intersect(target, visibleWinActual) Is Nothing ' returns TRUE if at least one cell in TARGET is currently visible on screen
            On Error GoTo 0
    
        End If
    End Function
user_19702430
Related