0

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
aliza
  • 45
  • 7
  • 2
    It is often helpful to post what you have tried, even if it doesn't work. That said, I believe [this](https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row#38882823) is what you're looking for – cybernetic.nomad Aug 27 '18 at 19:38
  • 1
    If A1 will never be blank, you could use `=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 the structured reference: `=SUBTOTAL(103,Table1[column_header])` – Ron Rosenfeld Aug 27 '18 at 19:53
  • A1 has the header. Actual data is from A2. I have just now added that in the edits. When I do `=Subtotal(103,A:A)-1` excel gives me a circular reference error – aliza Aug 27 '18 at 20:00
  • Well, you can't have the formula in column A – Ron Rosenfeld Aug 27 '18 at 20:13
  • Then you are putting the formula into column A. Use `=Subtotal(103, A2:A)` –  Aug 27 '18 at 20:13
  • Thank you Ron - I applied that formula in a different column and it worked!!! . All this time I was pulling my hair that why am I getting Circular reference error each time. But now when I applied it in another column it worked!!! wow lovely :-) How do I mark this question as solved now? – aliza Aug 27 '18 at 20:24
  • @alisa I'll post my suggestion as an answer, and you can then mark it as such. – Ron Rosenfeld Aug 27 '18 at 21:09

2 Answers2

1

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])
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

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)))