-1

how to count the number of rows with data based on the filter applied?

All I can find is methods like xlUp, xlDown which I cant apply to this as it will give me last row as opposed the number of rows with filtered data.

example

1    animal age
2    dog    10
3    cat    15

I apply the filter on cat and get the following table:

1    animal age
3    cat    15

with xlUp or down it will tell me last row number is 3, but obviously there is only 1 row with filtered data

Community
  • 1
  • 1
Mr.Riply
  • 825
  • 1
  • 12
  • 34

3 Answers3

5

The worksheet's SUBTOTAL function can count visible data in a column.

dim i as long
i = application.subtotal(103, columns(1))
debug.print i
4

What about this?, It is counting the visible cells in the first column in a filtered range.

Sub test()

data_visible_rows = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

End Sub
n8.
  • 1,732
  • 3
  • 16
  • 38
warner_sc
  • 848
  • 6
  • 13
  • 1
    This does require the concerned cells to be selected. If a cell is clicked outside of the desired region then this wouldn't return the desired value, I think. – n8. Mar 22 '18 at 16:39
  • Right, the range need to be specified – warner_sc Mar 22 '18 at 16:41
  • 2
    you can get around this by using `AutoFilter.Range.Columns(1)…` instead I think. – Pᴇʜ Mar 22 '18 at 16:41
  • Yeah good point, since autofilter behaviors are specified in the functionality. – n8. Mar 22 '18 at 16:42
  • @Pᴇʜ I'll appreciate if you could edit my answer with your suggestion, i really dont know how to implement it to my answer... – warner_sc Mar 22 '18 at 17:31
  • Edited again because the row number of the header isn't relevant. It must always be 1 subtracted since a header can't be more than one row. – n8. Mar 23 '18 at 20:40
0

Suppose you have data in Range A1:C3

enter image description here

and that you applied the filter manually

enter image description here

then the statement 

dim rng as Range
set rng = Range("$A$2:$C$3").SpecialCells(xlCellTypeVisible)

would return a reference to a Range with visible cells only. So you leave out the header.

you can then call

rng.Count / rng.Columns.Count

to obtain the desired result. Of course you could do it in one go without declaring anything. My previous explanation was for instructive purposes only.

Range("$A$2:$C$3").SpecialCells(xlCellTypeVisible).Count / Range("$A$2:$C$3").Columns.Count

This should work.

gioxc88
  • 503
  • 3
  • 18
  • This will only count the number of rows in the first contiguous visible area of the autofiltered range. – Pᴇʜ Mar 22 '18 at 16:49
  • this does what the op asks – gioxc88 Mar 22 '18 at 16:50
  • but only on his sample data and it will soon or later fail on real data and other criteria. e.g. add another data entry `4 cat 20` and it fails. It always count's only the header row + contiguous visible rows. And the OP asked for "*count the number of rows with data based on the filter applied?*" which means "*count all filtered rows*" and not only "*all contiguous visible rows (starting from header)*" – Pᴇʜ Mar 22 '18 at 16:58
  • you are right .. I will edit – gioxc88 Mar 22 '18 at 17:01
  • `Range("$A$2:$C$3").SpecialCells(xlCellTypeVisible).Count / Range("$A$2:$C$3").Columns.Count` This will also fail if there are invisible columns in between eg. if you hide column B. You need to divide by visible columns only (instead of all columns) – Pᴇʜ Mar 22 '18 at 17:11
  • I don't understand why you would divide anything. It seems complicated. – n8. Mar 22 '18 at 19:59
  • 1
    @n8. Well if you divide `total cell count` by `total column count` you get the `total row count` so `visible row count = visible cell count / visible column count` would be a valid solution. – Pᴇʜ Mar 23 '18 at 07:18
  • Aaaaaah I see. That's certainly the long way around though. – n8. Mar 23 '18 at 20:34
  • 1
    Kind of a rube goldberg divice. And prone to failure (#DIV/0) if all the rows are filtered. – n8. Mar 23 '18 at 20:35
  • fails even if you delete all data from your worksheet .. go get laid instead – gioxc88 Mar 23 '18 at 20:56