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.

The easy beginner way : just use SUBTOTAL worksheet function …

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.

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.

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.

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!