Count Values in Filtered List / Return Final Row in a Filtered List

1. Count Values in Filtered List / Return Final Row in a Filtered List

Afternoon,

I'm currently trying to return a variable integer based on the final row in a filtered table.

For example, when the table is unfiltered the last row / value count is 10.

When I filter the table, the last row / value count is 6, but the various pieces of code I'm using still return 10.

Does anyone have any ideas as to how I can dynamically reference the final row/value count?

My overall goal is to use this integer to give me the final point in which the code is to run too.

Many thanks,

Harry.

2. Re: Count Values in Filtered List / Return Final Row in a Filtered List

can you post your current used code to get the last row value?

3. Re: Count Values in Filtered List / Return Final Row in a Filtered List

The easy beginner way : just use SUBTOTAL worksheet function …

4. Re: Count Values in Filtered List / Return Final Row in a Filtered List

Originally Posted by MarMo
can you post your current used code to get the last row value?
Thanks for the reply MarMo, it's as follows:

PHP Code:
``` LastRow = DataSheet.Columns(2).Find("*", , , , xlByColumns, xlPrevious).Row  ```
Originally Posted by Marc L

The easy beginner way : just use SUBTOTAL worksheet function …

I tried this, but then realized that it would make the range too small.

I think the problem is slightly more complicated, I'm wanting to save and image of the filtered table, and i'm trying to use the LastRow variable to determine the end of the table, the problem is that the true end of the table is constant given that the size of the table doesn't change, only the content.

Any ideas?

Thanks again.

5. Re: Count Values in Filtered List / Return Final Row in a Filtered List

Try this.
``Please Login or Register  to view this content.``

6. Re: Count Values in Filtered List / Return Final Row in a Filtered List

Originally Posted by bakerman2
Try this.
``Please Login or Register  to view this content.``
Thanks Bakerman, before I run this, I'm wondering, I won't be able to set the data range without knowing the final row as the final row will determine the extent of the range.

If i just set the dataRange to be A1:A10000, would that serve to capture all regardless?

Thanks again.

7. Re: Count Values in Filtered List / Return Final Row in a Filtered List

I've done some further testing of that code Bakerman, it only gives me the first row underneath my headers. Not the last row in the filtered table.

8. Re: Count Values in Filtered List / Return Final Row in a Filtered List

HarryGreenwood,

Perhaps this is what you're looking for.
Try this code in your filtered table.
This gives me the exact rownumber in a filtered table, aasuming you're lloking for the lastrownumber, and not the address.
Otherwise you need to change the .Row into .Address

``Please Login or Register  to view this content.``
Hope this helps.

9. Re: Count Values in Filtered List / Return Final Row in a Filtered List

Originally Posted by MarMo
HarryGreenwood,

Perhaps this is what you're looking for.
Try this code in your filtered table.
This gives me the exact rownumber in a filtered table, aasuming you're lloking for the lastrownumber, and not the address.
Otherwise you need to change the .Row into .Address

``Please Login or Register  to view this content.``
Hope this helps.
Thanks MarMo, I actually used a similar piece of code to fix this, we came to the same conclusion!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1