Count only visible data after filtering

[571202]

​Hi, what do I need to add to the below code to only count the visible data after filtering. I assume it is possibly .SpecialCells(xlCellTypeVisible) or similar after With Worksheet("calculations"), but I can't figure it out. Help appreciated.

Sub Count_only_visible_data()With ActiveSheet
.Range("E5").Value = CountA("Work", "WSCA1", "Processed")
.Range("E6").Value = CountA("Work", "WSCA2", "Processed")
.Range("E7").Value = CountA("Work", "SECA11", "Processed")
.Range("E8").Value = CountA("Work", "SECA12", "Processed")
.Range("E9").Value = CountA("Work", "SECA13", "Processed")
.Range("E17").Value = CountA("Work", "NWCA5", "Processed")
.Range("E18").Value = CountA("Work", "NWCA6A", "Processed")
.Range("E19").Value = CountA("Work", "NWCA6B", "Processed")
.Range("E20").Value = CountA("Work", "NWCA7", "Processed")
.Range("E21").Value = CountA("Work", "NWCA8", "Processed")
.Range("E22").Value = CountA("Work", "NWCA9", "Processed")
.Range("E23").Value = CountA("Work", "NWCA10A", "Processed")
.Range("E24").Value = CountA("Work", "NWCA10B", "Processed")
.Range("E32").Value = CountA("Work", "WSCA3", "Processed")
.Range("E33").Value = CountA("Work", "WSCA4", "Processed")
.Range("E34").Value = CountA("Work", "SECA14", "Processed")
.Range("E35").Value = CountA("Work", "SECA15", "Processed")
.Range("E36").Value = CountA("Work", "SECA16", "Processed")
End With
End Sub
Function CountA(v1 As String, v2 As String, Optional v3 As String = "") As Long
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    With Sheets("calculations")
    
        Set rng1 = .Range("D7:D100000")
        Set rng2 = .Range("L7:L100000")
        Set rng3 = .Range("M7:M100000")
        If Len(v3) > 0 Then
            CountA = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3)
        Else
            CountA = Application.CountIfs(rng1, v1, rng2, v2)
        End If
    End With
End Function

 

[Andrew Fergus]

There is a native function in Excel that will do this: =SUBTOTAL(3, range)
Subtotal only works on visible cells after filtering, 3 uses the COUNTA function.
Either call this using worksheet function in VBA like this:
WorksheetFunction.Subtotal(3, ActiveSheet.Range(rng1))
or use the function within the spreadsheet and get the value from that cell.

 

[MARK858]

Subtotal(103, range)

Book1
	A	B	C
1	Function_num	Function_num	Function
2	(includes hidden values)	(ignores hidden values)
3	1	101	AVERAGE
4	2	102	COUNT
5	3	103	COUNTA
6	4	104	MAX
7	5	105	MIN
8	6	106	PRODUCT
9	7	107	STDEV
10	8	108	STDEVP
11	9	109	SUM
12	10	110	VAR
13	11	111	VARP
Sheet1

See the link below
https://support.office.com/en-us/article/SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939

 

[571202]

Sorry Andrew & Mark.....I have mislead you. It's a Countifs function, in my ignorance I just called this sub CountA because I have several and called them CountA, CountB etc. I will change that in my code to stop any confusion.

Am I able to do the same thing with Countifs?

I can't see where I can edit my code in my first post.....is it possible? Cheers

 

[MARK858]

See post number 4 in the link attached and adjust to your ranges/criteria.

https://www.mrexcel.com/forum/excel...ly-filtered-data-post5088027.html#post5088027

 

[571202]

Hi Mark, I see it is still 103, but I have no idea how to insert that into my VBA code. Are you able to show me?