CODEWITHSUNDEEP
vbaexcel
aliza
aliza

Reputation: 45

Find number of rows in an already filtered Column A in Excel

I have got an Excel spreadsheet. This spreadsheet has just one tab in it. The Tab name is dynamic in nature and changes every week also the number of rows.

I have column A filtered already with a VBA macro. A1 has the header. Now, I wanna find how many rows are there in this already filtered column A. I am looking for any VBA function. I have tried using Subtotal function.

=Subtotal(103,A2:A1345)

But I don't know the end range. As of now the end range is A1345. It will change every time in future if the new rows are added.

I tried multiple things but those did not work. I am quite new to VBA.

Upvotes: 0

Views: 91

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

If A1 will never be blank, you could use (in a column other than A)

=Subtotal(103,A:A)-1.

Or, if there will be data below your table not to be counted, then format your table as a Table and use structured references (and this formula could go into column A)

=SUBTOTAL(103,Table1[column_header])

Upvotes: 1

user4039065
user4039065

Reputation:

You can put the formula in column A if you use another column's last populated cell as the demarcation point.

If column B contains numbers then,

=subtotal(103, a2:index(a:a, match(1e99, b:b)))

If column B contains text then,

=subtotal(103, a2:index(a:a, match("zzz", b:b)))

Upvotes: 0

Related Questions