Count only visible data after filtering

571202

New Member
Joined
May 26, 2019
Messages
47
​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.
Code:
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top