How to count Visible rows if rows are filtered

ramakrishnan_1731

Board Regular
Dear Experts,

If filtered using auto filter how to count the rows.

If i give =rows( full range ) after filtering it should be equal to the no of rows displayed.

Is there any formula to count the visible rows after filtering.

regds
Ramakrishnan

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Datsmart

Well-known Member
Take a look at the SUBTOTAL function. It was designed to only tally visible values.

ramakrishnan_1731

Board Regular
Take a look at the SUBTOTAL function. It was designed to only tally visible values.

it work only for numeric values. if i am using text how will it work ?

is there any way to find count of visible text in rows?

ramakrishnan_1731

Board Regular
Pls

Take a look at the SUBTOTAL function. It was designed to only tally visible values.

it work only for numeric values. if i am using text how will it work ?

is there any way to find count of visible text in rows?

is there any other way with out using VB.

ramakrishnan_1731

Board Regular
Re: Pls

Take a look at the SUBTOTAL function. It was designed to only tally visible values.

it work only for numeric values. if i am using text how will it work ?

is there any way to find count of visible text in rows?

is there any other way with out using VB.
.

MrExcel MVP
Re: Pls

Take a look at the SUBTOTAL function. It was designed to only tally visible values.

it work only for numeric values. if i am using text how will it work ?

is there any way to find count of visible text in rows?

is there any other way with out using VB.
.

=SUBTOTAL(3,Range)

Range refers to the range, subject to AutoFilter.

HalfAce

MrExcel MVP
Hello ramakrishnan_1731,
Which function number did you use?
To return how many rows of filtered text are visible, you can use COUNTA (function number 3)
(ie - if your range were A1:A50)
=SUBTOTAL(3, A1:A50)

Hope it helps.

dave3009

Well-known Member
Does this help

Code:
``````Sub CountVisibleRows()
Dim c As Range
Dim my As Range
Set my = Sheet1.Range("A1:A65536")
For Each c In my
If c.EntireRow.Hidden = False Then a = a + 1
Next
MsgBox a
End Sub``````

Kind Regards

Dave

Replies
20
Views
3K
Replies
2
Views
617
Replies
6
Views
1K
Replies
5
Views
2K
Replies
5
Views
667

1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

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.

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

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