+ Reply to Thread
Results 1 to 19 of 19

Find last row including hidden and filtered rows

  1. 11-29-2009, 03:56 PM #1
    jpruffle
    jpruffle is offline
    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.
    Reply With QuoteRegister To Reply
  2. 11-29-2009, 04:01 PM #2
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  3. 11-29-2009, 04:11 PM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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
    Reply With QuoteRegister To Reply
  4. 11-29-2009, 04:12 PM #4
    tigertiger
    tigertiger is offline
    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.
    Reply With QuoteRegister To Reply
  5. 11-29-2009, 04:18 PM #5
    jpruffle
    jpruffle is offline
    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
    Reply With QuoteRegister To Reply
  6. 11-29-2009, 04:29 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Reply With QuoteRegister To Reply
  7. 11-29-2009, 04:37 PM #7
    jpruffle
    jpruffle is offline
    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!
    Reply With QuoteRegister To Reply
  8. 11-29-2009, 05:00 PM #8
    jpruffle
    jpruffle is offline
    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?
    Reply With QuoteRegister To Reply
  9. 11-29-2009, 05:18 PM #9
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Reply With QuoteRegister To Reply
  10. 11-29-2009, 05:54 PM #10
    jpruffle
    jpruffle is offline
    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
    Reply With QuoteRegister To Reply
  11. 11-29-2009, 06:20 PM #11
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Reply With QuoteRegister To Reply
  12. 11-29-2009, 07:28 PM #12
    jpruffle
    jpruffle is offline
    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
    Reply With QuoteRegister To Reply
  13. 11-29-2009, 07:34 PM #13
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Reply With QuoteRegister To Reply
  14. 08-03-2016, 12:43 PM #14
    newtonc
    newtonc is offline
    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
    Reply With QuoteRegister To Reply
  15. 08-03-2016, 12:57 PM #15
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Find last row including hidden and filtered rows 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 09-03-2019, 03:37 AM #16
    Un-Do Re-Do
    Un-Do Re-Do is offline
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    549

    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/
    Reply With QuoteRegister To Reply
  17. 09-03-2019, 05:41 AM #17
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    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...
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  18. 09-03-2019, 10:59 PM #18
    Un-Do Re-Do
    Un-Do Re-Do is offline
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    549

    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.
    Reply With QuoteRegister To Reply
  19. 09-04-2019, 12:08 AM #19
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1