+ Reply to Thread
Results 1 to 9 of 9

Auto Hide & Unhide Rows/Create Lists Based Off Value

  1. #1
    Registered User
    Join Date
    03-26-2021
    Location
    Fort Worth, TX
    MS-Off Ver
    365
    Posts
    46

    Auto Hide & Unhide Rows/Create Lists Based Off Value

    So I'm using a formula that find invoices that meet a certain requirement. The problem is that the lookup formula requires the uses of 100 rows. So I was thinking of using a macro that auto hides blank cells based off their value to make a neat looking list. The rules for it are pretty simple:
    - If a cell in column A = "" then hide row
    - If a cell in column A<>"" then show row


    If you think it would be easier to generate a list instead then the formula I'm using for it is:
    AND($C6<>"", $J6>=29)

    I've attached a doc to better show what I'm trying to do. Any help would be appreciated! Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    796

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    its def doable, but how would you want to handle the empty rows between the sections?

    If that number of rows for each vendor is not always the same, then some calculations(logic) will need to be done to determine the last row for a vendor, so that your total and space between vendors stays there

    I think you would want to loop thru specific ranges within column A and then call .EntireRow.Hidden = True to set those that are empty to hidden.
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    796

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    Here is a quick sample of how you can do it on your sheet, this works for the first vendor and needs to be adjusted to accommodate how you determine each vendor set.

    Please Login or Register  to view this content.
    The only thing with this logic is that as its hiding the rows, the row count is affected and doesnt make it past the last row with data. but its something to look at and try to use and adjust for your needs.

  4. #4
    Registered User
    Join Date
    03-26-2021
    Location
    Fort Worth, TX
    MS-Off Ver
    365
    Posts
    46

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    I was thinking of just having multiple subs for each vendor in my example and just calling them all with a single macro. So the first section would only look through rows 1-150 and the next function would be 155-250, ect. As for the function since the formula is just set to blank if nothing is found the I was thinking of just having the VBA go through each row and checking if cell A in that row is blank and then hide it or unhide it if not. Coding wise I think it would be pretty simple. Only issue is I'm not good at coding.

  5. #5
    Registered User
    Join Date
    03-26-2021
    Location
    Fort Worth, TX
    MS-Off Ver
    365
    Posts
    46

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    I see what you're talking about. While I can't write code, I do understand it. Would it be possible to just make it a loop function so it loops through rows 4 to 24 until there are no blank cells? Also it can't check hidden rows for new entries so maybe we could try having the first command be unhide rows 4:24 then loop through them? Thanks

  6. #6
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    796

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    if you know the rows will be constant, then yes you can change that to just loop thru rows 4 thru 24 to hide and to unhide same thing..

    To unhide any hidden rows from 4 to 24
    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-26-2021
    Location
    Fort Worth, TX
    MS-Off Ver
    365
    Posts
    46

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    Is it possible to tell it to stop at row 24? not count 24 rows?

  8. #8
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    796

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    Not sure i understand your question, the above will only loop thru rows 4 until 24 and stop, it wont go any further past 24.

  9. #9
    Registered User
    Join Date
    03-26-2021
    Location
    Fort Worth, TX
    MS-Off Ver
    365
    Posts
    46

    Re: Auto Hide & Unhide Rows/Create Lists Based Off Value

    That's fine, I messed around with it a bit and got it to work. Thanks

+ 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. [SOLVED] auto hide and unhide rows based on formula result in a cell
    By Dena in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-13-2017, 05:46 PM
  2. [SOLVED] How to auto hide/unhide rows based on value of sum
    By wesleeptheylive in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-27-2013, 04:34 PM
  3. [SOLVED] Auto Hide and Unhide Rows Based
    By Kosmik in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-05-2013, 10:09 AM
  4. [SOLVED] Please help with this monster! Need help w/VBA codes to hide and unhide rows base on lists
    By SeniaHdz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-16-2013, 12:40 PM
  5. Auto Hide/Unhide rows in Excel based on radio button selection
    By awill110 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2013, 03:05 PM
  6. Auto Hide/Unhide rows in Excel based on formula result in a column
    By crozierk in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-12-2012, 08:02 AM
  7. Hide/Unhide rows Based on Value from Dropdown Lists
    By 2Deep in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2010, 09:43 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