+ Reply to Thread
Results 1 to 19 of 19

Find last row including hidden and filtered rows

  1. #1
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Find last row including hidden and filtered rows

    Hi,

    I normally use the following code to add a row of data via a userform:

    Please Login or Register  to view this content.
    This works fine unless I have hidden or filtered rows. In which case it overwrites an existing row of data. Is there a way I can reference the very last row including hidden adn filtered data?

    Thanks in advance.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Find last row including hidden and filtered rows

    there was a thread kicked off on this subject over at MrE some time back and given some notable contributors I'm sure no one will mind me referencing it here as point of interest:

    http://www.mrexcel.com/forum/showthread.php?t=422595

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Find last row including hidden and filtered rows

    Try this:
    Please Login or Register  to view this content.
    EDIT: Just reread your title, sorry. That accomodates hidden rows but not filtered rows.
    Last edited by shg; 11-29-2009 at 04:15 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Find last row including hidden and filtered rows

    According to Nuovella, Plz, see the detail under the link: http://excelvbamacro.com/how-to-find...data-in-excel/


    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: Find last row including hidden and filtered rows

    Thank you all for your swift replies. The MrE thread was very interesting but with my very limited knowledge possed more questions than answers as to the best method.

    How do you call a function from existing code?

    My code at present is:

    Please Login or Register  to view this content.
    Thank you in advance

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Find last row including hidden and filtered rows

    TT,

    The UsedRange includes cells that are formatted but otherwise empty. I doubt that's what the OP is after.

  7. #7
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: Find last row including hidden and filtered rows

    That is correct SHG I most of my rows are formatted I need to just ascertain the last row within the data range including hidden and filtered data...

    Can anyone guide me on the best way?? My knowledge is poor at best!

  8. #8
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: Find last row including hidden and filtered rows

    On my code above how would I call the functions mentioned or write that into my code?

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Find last row including hidden and filtered rows

    DO's post in the thread he linked looks good to me:
    Please Login or Register  to view this content.
    Then in your code,
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: Find last row including hidden and filtered rows

    Thank you that does work great without formatting.

    However it adds the new row at the end of the formatting. On most of my sheets I have conditional formatting for 200+ rows with no data on. It adds the new row of data after that.

    Can that be stopped?

    Thanks in advance

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Find last row including hidden and filtered rows

    I don't see that conditional formatting affects it. Select the rows you think are clear but for formatting and hit the Delete key.

  12. #12
    Forum Contributor
    Join Date
    06-03-2008
    MS-Off Ver
    Excel 2007 (also have access to Excel 2000/2003)
    Posts
    368

    Re: Find last row including hidden and filtered rows

    Sorry it was me! I have formula all the way down a column. Is there a way to get this function to work using just references to blank columns? For example column E has a formula in but the rest remain blank. Can it work off say column A only?

    Thanks for your patience and help

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Find last row including hidden and filtered rows

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

  14. #14
    Registered User
    Join Date
    06-20-2013
    Location
    nt
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Find last row including hidden and filtered rows

    None of the code posted here works with filtered rows. Does anyone knows how i may find the last row if it's filtered? thanks

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Find last row including hidden and filtered rows

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    504

    Re: Find last row including hidden and filtered rows

    Would this be useful? I have not tested yet.
    https://greghitchon.wordpress.com/20...true-last-row/

  17. #17
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,486

    Re: Find last row including hidden and filtered rows

    The original post is ten years old. I suppose the OP found the solution by now...
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  18. #18
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    504

    Re: Find last row including hidden and filtered rows

    The original post is ten years old. I suppose the OP found the solution by now...
    Yes but the intention is that others may benefit.
    The post comes up in a search engine.

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,232

    Re: Find last row including hidden and filtered rows

    Un-Do Re-Do, thanks for the input. I dong think pepe was being negative, just pointing out the age of the thread. Im sure others will find that useful
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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