Excel to CountIF in filtered data

Question

I am trying to count the number of occurrences of a specific string in filtered data. I can do it using a formula in a cell but when I combine that with the other macros in my workbook the whole thing freezes.

So I would like to move the calculation to VBA so that it only calculates when the macro is run. Here is the formula that works in the cell:

=SUMPRODUCT(SUBTOTAL(3,OFFSET('2015 Master'!H:H,ROW('2015 Master'!H:H)-MIN(ROW('2015 Master'!H:H)),,1)),ISNUMBER(SEARCH("*Temp*",'2015 Master'!H:H))+0)

Basically I want to count the number of times "Temp" occurs in column H but only in the filtered data.

Thank you for your help!

ADDITION:

Here is the code I've written for the macro so far. It filters the data on a different sheet then updates the pivot table with the date range. I would like to add the count calculations to the end of this code and return the count to a cell on the 'Reporting' sheet.

Sub Button1_Click() 'Refresh the pivot table and all calculations in the active sheet

ActiveWorkbook.RefreshAll

'Gather the start and end times from the active sheet

dStart = Cells(2, 5).Value
dEnd = Cells(3, 5).Value

'Change the active sheet to the alarms database, clear all filters and then filter for the defined date range and filter for only GMP alarms

Sheets("2015 Master").Select

If ActiveWorkbook.ActiveSheet.FilterMode Or ActiveWorkbook.ActiveSheet.AutoFilterMode Then
ActiveWorkbook.ActiveSheet.ShowAllData
End If

ActiveSheet.ListObjects("Table44").Range.AutoFilter Field _
    :=3, Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:= _
    "<=" & dEnd

Range("Table44[[#Headers],[GMP or non-GMP]]").Select
ActiveSheet.ListObjects("Table44").Range.AutoFilter Field:=2, Criteria1:= _
    "GMP"
'Change the active sheet to the Reporting sheet

Sheets("Reporting").Select

'Within the alarms pivot table clear the label filters then filter for the date range and GMP alarms

ActiveSheet.PivotTables("PivotTable1").PivotFields("Active Time"). _
    ClearLabelFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Active Time").PivotFilters. _
    Add Type:=xlDateBetween, Value1:=dStart, Value2:=dEnd
ActiveSheet.PivotTables("PivotTable1").PivotFields("GMP or non-GMP"). _
    CurrentPage = "GMP"
 End Sub

Answer 1

Pertinent to clarified question topic (i.e. " Basically I want to count the number of times "Temp" occurs in column H..."), the VBA solution can be as shown in the following code snippet. Assuming sample data entered in Column "H":

H

Temp Directory on C: Drive
Temp Directory
Project Directory
Output Temp Directory
Start Directory
Temp obj

apply the VBA Macro:

Sub CountTempDemo()
Dim i As Integer
Dim count As Integer
Dim startRow As Integer
Dim lastRow As Integer
Dim s As String

startRow = 2 'or use your "filtered range"
lastRow = Cells(Rows.count, "H").End(xlUp).Row 'or use your "filtered range"
count = 0
For i = 2 To lastRow
    If InStr(Cells(i, 8).Value, "Temp") > 0 Then
        count = count + 1
    End If
Next
End Sub

where count value of 4 is a number of "Temp" occurrences in specified "H" range.

Hope this may help. Best regards,

Answer 2

To iterate over a column and find only visible (unfiltered) cells, one way is this:

Set h = ... Columns ("H");
Set r = h.SpecialCells(xlCellTypeVisible)
' now r is a composite range of potentially discontiguous cells 
' -- it is composed of zero or more areas
'but only the visible cells; all hidden cells are skipped
Set ar = r.Areas
for ac = 1 to ar.Count
    Set rSub = ar(ac)
    'rSub is a contiguous range
    'you can use a standard formula, e.g. Application.WorksheetFunction.CountIf(...)
    'or loop over individual elements
    'and count what you like
next

caveats: if any rows (or the column) are hidden manually (not from filtering) the count using this method will consider them as filtered (i.e. hidden/not visible).

---

Update: answer to comment

A Range is really a very general purpose notion of an aggregation of cells into a grouping or collecting object (the Range). Even though we usually think of a Range as being a box or rectangle of cells (i.e. contiguous cells), a Range can actually assemble discontiguous cells.

One example is when the user selects several discontiguous cells, rows, and/or columns. Then, for example, ActiveSheet.Selection will be a single Range reflecting these discontiguous cells. The same can happen with the return value from SpecialCells.

So, the Excel object model says that in general, a Range can be composed of Areas, where each Area itself is also represented by a Range, but this time, it is understood to be a contiguous Range. The only way you can tell if the Range is contiguous or not is if you created it as a box/rectangle, or, if Areas.Count = 1.

One way to investigate a bit more might be to select some discontiguous cells, then enter a macro and use the debugger to observe Selection.