Closed Thread
Results 1 to 14 of 14

Automatically hide and unhide rows based on value in cell

  1. #1
    Registered User
    Join Date
    02-11-2007
    Posts
    19

    Automatically hide and unhide rows based on value in cell

    First - thanks in advance for the help!

    Using Excel 2007

    Cell D3 is where I want the user to input a value between 1 and 2000. This value represents how many rows to show starting at row 6 and ending at row 2005.

    Cell D3 row # to be shown
    1 6
    2 6-7
    10 6-15
    100 6-105
    500 6-505
    2000 6-2005

    But more importantly, say cell D3 currently has 480 and rows 6-485 are showing. If the user changes cell D3 from 480 to 7, then only rows 6-12 should be shown and rows 13-2005 would now be hidden.

    I need the hiding & unhiding to occur on the event of Cell D3 changing.

    Please provide the VBA code as I don't know how to write VB code.

    Again - thank you, thank you, thank you!!!!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,816

    Re: Automatically hide and unhide rows based on value in cell

    Try this code behind the worksheet:
    Please Login or Register  to view this content.
    Last edited by MarvinP; 03-09-2011 at 09:03 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

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

    Re: Automatically hide and unhide rows based on value in cell

    Quote Originally Posted by kmham
    Cell D3 is where I want the user to input a value between 1 and 2000. This value represents how many rows to show starting at row 6 and ending at row 2005.
    Perhaps:

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 03-09-2011 at 04:35 AM. Reason: added quote

  4. #4
    Registered User
    Join Date
    02-11-2007
    Posts
    19

    Re: Automatically hide and unhide rows based on value in cell

    Thank you both. When I insert the code Excel asks me to create a name for the macro and I end up with your code followed by a break line with Sub ShowRows() and End Sub. I named the marco "ShowRows". I don't think this hurts anything, but mentioning just in case.

    Anyway, for both of the above, they do make rows hide and show, but they seem to work in reverse. I input "2000" into Cell D3 and frows 6-2006 are hidden (2006 instead of 2005) - using the code from DonkeyOte here. I input "10" into Cell D3 and rows 6-16 are hidden and rows 17+ are showing.

    I'm sure that I'm doing someone wrong so I'll keep trying to see what needs tweaked on my end. I've attached a screen shot if that helps any.
    Attached Images Attached Images

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

    Re: Automatically hide and unhide rows based on value in cell

    Quote Originally Posted by kmham
    When I insert the code Excel asks me to create a name for the macro
    The code provided is Event driven (so as to run automatically as and when D3 is manually altered) - as such the code should be placed within the appropriate Sheet Object in VBE.

    To do this correctly: in native XL right click on the Sheet Tab to which the code is to be applied select View Code and paste code into resulting window.

    Quote Originally Posted by kmham
    using the code from DonkeyOte here. I input "10" into Cell D3 and rows 6-16 are hidden and rows 17+ are showing.
    Not so - if D3 is 10 using my code rows 6:16 will be visible and 17+ hidden.

    I'd suggest clearing whatever code you added and then given the above advice start over - post back if you have further queries.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,816

    Re: Automatically hide and unhide rows based on value in cell

    When I first read DO's code it occurred to me we differed in our solution. I figured you would chose the one that fit your needs.

    Both should run if put behind the worksheet, as event code. Comparing then should give you a good start on writing your own routines.

  7. #7
    Registered User
    Join Date
    10-27-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Automatically hide and unhide rows based on value in cell

    hi sorry to be a pain, but i have a similar problem but i guess it is ever so slightly different.

    i want row 7 to appear if cell I6 says No. But if it says Yes i want it to go on to row 8.
    Then for row 9 to appear if cell I8 says No and again i then want it to go to row 10 if it says Yes. etc

    i hope some one will be able to help. it has been annoying me for a while and i know you guys will hopefully know the answer.

    Kind regards

    Marc

  8. #8
    Registered User
    Join Date
    07-18-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Automatically hide and unhide rows based on value in cell

    Donkey,

    I like your code you used, at it works well with what I am doing with my excel file, but I was wondering is there a way to make multiple sections collapse that are located on another sheet, instead of just one section on the same sheet?

    What I mean is the code will collapse the rows that are specified, but what if I have 4 other sets of rows that I would like collapsed as well on various sheets?

    Example:
    Rows 10:34 will collapse depending on what is input into cell D3 of the same sheet (Sheet 1)

    Rows 14:38 & Rows 43:67 & Rows 72:96 on Sheet 2 will collapse depending on what is input into cell D3 of Sheet 1

    Rows 14:38 on Sheet 3 will collapse depending on what is input into cell D3 of Sheet 1


    I am not a great code writer, and I have no idea how to make this work, but I know it is possible. I am using Excel 2007.

  9. #9
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Automatically hide and unhide rows based on value in cell

    Hello bacon1987, and welcome to the forum.

    Unfortunately you've broken one of the forum rules. Please read the following. Thanks.

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

  10. #10
    Registered User
    Join Date
    08-12-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Automatically hide and unhide rows based on value in cell

    This thread is great! Is there any way to hide both rows and columns based on a cell value? I can follow hiding the rows as outlined here, but let's say I want to hide all columns greater than F and hide all rows (like above) -- how would I add the hide column code?

  11. #11
    Registered User
    Join Date
    01-08-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Automatically hide and unhide rows based on value in cell

    This thread got so much useful information

  12. #12
    Registered User
    Join Date
    05-28-2013
    Location
    dubai
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Automatically hide and unhide rows based on value in cell

    Thanks it was very much useful

  13. #13
    Registered User
    Join Date
    01-24-2014
    Location
    North East
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Automatically hide and unhide rows based on value in cell

    Hi all,

    I need a little bit of help with an excel sheet I'm working on, I need a macro to automatically unhide/hide rows if a cell is a set value (See details below):

    Ok so cell J9 has a drop down box with various different error codes, if the selected error code is "B5 - Out of stock" I need the macro to automatically unhide rows 11:18.

    If it is any other error code, I need it to hide rows 11:18.

    Any help will be much appreciated!

    Thanks,

    Ben

  14. #14
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Automatically hide and unhide rows based on value in cell

    Welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed 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