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!!!!

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.

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

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.

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.

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?

This thread got so much useful information

Thanks it was very much useful

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