CODEWITHSUNDEEP
vbaexcel
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Subtotal with CountA always return 0 when trying to find filtered rows count

Ive sheet which is filtered using few criterias. Then I need to find the filterd rows count. So for that purpose I used following code.

rCount = Application.WorksheetFunction.Subtotal(3, Sheets("Report_bar").Range("A2:A500000"))

But rCount always shows as Zero

I dint understand what I did there wrong.

Upvotes: 0

Views: 911

Answers (1)

QHarr
QHarr

Reputation: 84465

It will depend on your requirements and data. You are using SUBTOTAL 3 which is COUNTA

Just ignoring hidden rows:

1) Use AGGREGATE function with 3,5 (or 2,5 if want Count)

rCount = Application.WorksheetFunction.Aggregate(3, 5,Sheets("Report_bar").Range("A2:A500000"))

OR

2) SUBTOTAL with 103 (or 102 if want Count)

rCount = Application.WorksheetFunction.Subtotal(103, Sheets("Report_bar").Range("A2:A500000"))

Notes on referenced items:

But you could also choose with AGGREGATE functions to ignore errors rows and a variety of other choices. Take a look through the different choices in the documentation.

1) AGGREGATE(function_num, options, ref1, [ref2], …)

Function_num 2 = COUNT

Function_num 3 = COUNTA

Option 5 = Ignore hidden rows

2) SUBTOTAL(function_num,ref1,[ref2],...).

102 COUNT Ignore hidden rows

103 COUNTA Ignore hidden rows

EDIT:

Thanks to @Jeeped for noting: "subtotal's 3 is documented as all rows with 103 to ignore hidden rows but in reality both 3 and 103 work exactly the same"

Upvotes: 2

Related Questions