+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    12-02-2014
    Location
    England
    MS-Off Ver
    2010 | 2016
    Posts
    167

    Cool 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. #2
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365
    Posts
    274

    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?
    Even a little help can be a big help !!
    1. A sample workbook says more then words. Add problem description and solution so we can understand the problem.
    2. Your appreciation is accepted by clicking the star "Add Reputation" at the lower left of the post.
    3. If your problem is solved, mark it as [SOLVED]: See "FAQ : " https://www.excelforum.com/faq.php
    4. Use [CODE] [/CODE] tags to illustrate your code: see here

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,501

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


    The easy beginner way : just use SUBTOTAL worksheet function …

  4. #4
    Forum Contributor
    Join Date
    12-02-2014
    Location
    England
    MS-Off Ver
    2010 | 2016
    Posts
    167

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

    Quote Originally Posted by MarMo View Post
    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("*", , , , xlByColumnsxlPrevious).Row 
    Quote Originally Posted by Marc L View Post

    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. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

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

    Try this.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Forum Contributor
    Join Date
    12-02-2014
    Location
    England
    MS-Off Ver
    2010 | 2016
    Posts
    167

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

    Quote Originally Posted by bakerman2 View Post
    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. #7
    Forum Contributor
    Join Date
    12-02-2014
    Location
    England
    MS-Off Ver
    2010 | 2016
    Posts
    167

    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. #8
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365
    Posts
    274

    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.
    Last edited by MarMo; 06-22-2018 at 07:04 AM.

  9. #9
    Forum Contributor
    Join Date
    12-02-2014
    Location
    England
    MS-Off Ver
    2010 | 2016
    Posts
    167

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

    Quote Originally Posted by MarMo View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to count unique values in filtered list?
    By Cayenne in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 10-23-2014, 04:58 PM
  2. [SOLVED] Excel VBA - Issue in Naming Filtered Range on a Filtered List.
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-06-2014, 01:17 PM
  3. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  4. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Domenic in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 11:05 AM
  5. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

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