+ Reply to Thread
Results 1 to 7 of 7

Auto Hide Rows Based on Value in Column

  1. #1
    Registered User
    Join Date
    11-30-2011
    Location
    Syracuse, NY
    MS-Off Ver
    MS 365
    Posts
    14

    Auto Hide Rows Based on Value in Column

    This one seems like a very common need, and I have tried all sorts of macros that do an ok job, but I need an AUTOMATIC solution as well.
    It just has to hide all ROWS where column A has a certain value, such as "hide_me". That's it.
    (It would be even better if I had a macro version that could run on demand, and an automatic version that runs all the time, using similar VBA construction.)
    I don't think an example workbook is needed here.

  2. #2
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    929

    Re: Auto Hide Rows Based on Value in Column

    A sample worksheet would be helpful. Nonetheless, check out the last item in this thread. I just implemented a hide row solution.https://www.excelforum.com/excel-pro...ml#post5362812. Let me know your thoughts

  3. #3
    Registered User
    Join Date
    11-30-2011
    Location
    Syracuse, NY
    MS-Off Ver
    MS 365
    Posts
    14

    Re: Auto Hide Rows Based on Value in Column

    I have attached the "staffing by week" workbook sample. It is from a much larger file, so I converted the formulas to values for simplicity.

    In Columns A and B I will create formulas that yield values I will use to hide Rows, such as "hide" and "autohide".
    I have a macro that hides all the rows where "hide" appears in Column B, but that's for later attachment to a button.
    I need a VBA to hide all Rows whenever Column A changes to "autohide", and unhide them when Column A changes to something other than "autohide".
    I'd like to be able to reference named ranges instead of hard coding ranges in the VBA, but that's not a deal breaker.
    there are many post on line that claim to do this, but they don't work automatically, only as manually run macros.
    Any help is appreciated.
    Attached Files Attached Files
    Last edited by scottrperry; 07-10-2020 at 12:52 PM.

  4. #4
    Registered User
    Join Date
    11-30-2011
    Location
    Syracuse, NY
    MS-Off Ver
    MS 365
    Posts
    14

    Re: Auto Hide Rows Based on Value in Column

    Quote Originally Posted by maniacb View Post
    A sample worksheet would be helpful. Nonetheless, check out the last item in this thread. I just implemented a hide row solution.https://www.excelforum.com/excel-pro...ml#post5362812. Let me know your thoughts
    I replied with workbook on 7/10. I hope you can see what I'm going for.

  5. #5
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    929

    Re: Auto Hide Rows Based on Value in Column

    My solution does involve code and hides a row automatically when autohide is entered into the A column. It requires a deactivate button too. I added your code to the selectionChange Event. Let me know if you have questions.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-30-2011
    Location
    Syracuse, NY
    MS-Off Ver
    MS 365
    Posts
    14

    Re: Auto Hide Rows Based on Value in Column

    Thanks, maniacb.

    that works. it's not pretty when it executes, but it works.
    I wish my company would just learn to love Groups.

    I really need to dive into VB for myself.
    Is there a way to pull the range ("a5:a100") from a cell? Specifically the cell where the activate button lives? It would allow me to have several buttons to activate the same code with differing (dynamic) ranges.
    If so, could I create a named range and have the button cell text value ="namedrange", and code it as range("namedrange")?
    Last edited by scottrperry; 07-16-2020 at 12:59 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    929

    Re: Auto Hide Rows Based on Value in Column

    I don't understand your question:
    Is there a way to pull the range ("a5:a100") from a cell?
    Can you expand on it.

+ 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 Rows Based on Row Cell Value of NA
    By DaveLet in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2019, 11:50 AM
  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. NEED IMMEDIATE HELP - Auto-Hide Rows Based on Data in two columns
    By DLavoie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2013, 12:58 PM
  4. [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
  5. want to auto-hide rows based on a value in that row
    By jimboryan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2013, 12:24 PM
  6. [SOLVED] Auto Hide Rows Based on Cell Selection
    By tlfaille in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2013, 12:54 PM
  7. 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
  8. auto hide of rows based on column info
    By petelomax in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-27-2012, 07:43 AM

Tags for this Thread

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