Hide and unhide multiple rows in Excel 2007 by a single mouse click


In Excel 2007, what would be the easiest way to create "buttons" that hide and/or unhide certain rows?  I have a spreadsheet with several hundred rows of data in multiple columns that all relate together with various formulas.  From this data are several views I want to be able to obtain with the click of a mouse.  The views involve nothing other than hiding some rows and unhiding others.  The best scenario would be to have designated cells at the top of the spreadsheet, each of which corresponds to a specific view and each of which, when clicked, would produce that view.  Is there a way to do that?  I was thinking it might be possible with a macro or maybe by using the Filter function.  Would either of those work or is there a simpler solution?

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TracyVBA DeveloperCommented:
The filter function would be the easiest.  Add a new field in Column A and call it View, then add your views to the different rows.  Then add an autofilter, and just select the view you want to see.

See attached for simple example.

Do you have any knowledge of VBA?
Steve_BradyAuthor Commented:
Thanks for the quick responses and the attachment.

I considered the filter option but my concern is that many of the rows are included in more than one view, i.e. if I simplify and say there are only three views: A, B, and C, is it possible to use Filter to open rows which not only "equal" the filtering parameter, but "contain" it?

If this example was placed in your attachment:

Row   Param
  1          B
  2          C
  3          A,C
  4          A
  5          C
  6          B,C

Filtering for A would match only Row 4.
Filtering for B would match only Row 1.
Filtering for C would match only Rows 2 & 5.

Could the filter be configured so that:

Filtering for A would match Rows 3 & 4?
Filtering for B would match Rows 1 & 6?
Filtering for C would match Rows 2, 3, 5, & 6?

That is really what I need.

No, unfortunately I have not yet learned VBA.

Thanks again.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Saurabh Singh TeotiaCommented:
When you apply filter dont apply filter where you save value equal to A, rather apply filter saying value contains-->A so if you apply filter value contains A both Row 3&4 will be filtered.
Similarly when you do filter saying value contains-->B both row 1 and 6 will be filtered.
And similarly if you apply filter saying value contains-->C rows-->2,3,5 and 6 will be filtered.

you can use "Advanced Filters" instead of the automatic filters (see help file in Excel for a complete description). This method uses criteria cells inside the sheet where you can define nearly any kind of complex filter. The disadvantage is that you can delete the filter, but to set it again you must first select the complete table which should be filtered, then select the complete criteria area and then you can apply the filter. As you must do that every time I made a little macro with the macro recorder which does the job with one click.
You only need a criteria expression like this:


to get all rows containing a "C". Change the filter like you want and click "Set Filter".
As this example is very simple you should not have any problem to adjust it to your need. Simply change the range values in the macro to fit to your own table.




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve_BradyAuthor Commented:

Your suggestion sounds like it would be a good solution but can you tell me how to filter for "contains" rather than "equal to?"  I have not been able to figure out how to do that.


Same question I asked Saurabh.  Also, you said that I "can" delete the filter but does that necessarily mean the filter automatically gets deleted somehow?  For example, is it not saved with the file when I close the application?  Also, in what situations would I need to re-select the table (Is that the same as data range?) and criteria are?  I'm sorry that I don't understand your comment.

Furthermore, (again I apologize but) I don't understand what


represents or does. I tried it in a formula or two but could not determine whether this.

Finally, when I tried to run your sample macro I got an alert saying, "Cannot run the macro 'Filter...'.  The macro may not be available in this work book or all macros may be disabled."  I don't generally use macros but I don't know why they would be disabled or how to check that. However, depending on your explanation to the above, I may not need the macro.


Saurabh Singh TeotiaCommented:
When you apply filter it gives you an option of what kind of filter you want to apply so in there instead of equal select the option of contain which will basically do what you are looking for...

you will find the most answers in the Excel help under "Advanced Filter" which has a good and very long explanation about the different possibilities.
As you can see in the example the filter is applied through an extra line containing the mentioned expression. The asterisk is a joker and means "any number of characters" like the joker in DOS (with the difference that you can use an asterisk joker in DOS only as last character).
If you click on the "Data" tab in Excel you will find the advanced filter at the right of the normal filter icon. If you click it, a dialog opens where you must enter the range of the table which should be filtered and the range of the criteria which should be applied as filter. This must be done every time you apply the filter again or change something in the criteria which is very annoying. That's what the macro does automatically - but you don't need it, you can do it manually as described.
As the filter criteria is a normal cell it is of course saved with the file, also the filter as long as it is enabled - samelike with the auto filter.

A filter criterium can be for example:


which would filter out any rows less or equal than 100 as value. The only special case is the equal sign as this is used in Excel to introduce a formula. In this case you need the special syntax to first write an equal sign followed by a double quote and then again an equal sign and then the filter. That's why in this case the little bit strange syntax


would be needed to filter everything which has the value 100.
The "*C*" means: show me any row which contains the letter C.

Macros are by default disabled in Excel 2007. You will get a security warning below the ribbon with an option button. Click this and enable the macro in the worksheet, then you can use it.

You can go to the Excel options in the "Often used" tab (the first) and then activate the checkbox for "developer extension", that will enable a further tab in the ribbon where you can start the Visual Basic Editor to see the macro (containing only two lines of code).

Moreover, if this is no problem for you, you can go to the trust center in the Excel options and permanently activate macros if you don't want to activate it by button each time. But think about it, this option were introduced to make Excel sheets more secure and protect a little bit better against viruses. So in the most cases it's better to click the button and enable it instead every time you open the file.



Steve_BradyAuthor Commented:

Wow!  That response was above and beyond...  Thanks for taking the time to explain it. I spent quite a bit of time going through the help documentation but I think I understand it much better now -- I even got the macro working.  Oh, and I have never really been clear on using "*"s as wildcards -- thanks for that too.

If I have got this right, what you were explaining is that you cannot just go in and change the parameter by hand and have the filtering change.  Is that correct? If so, it seems odd that you cannot change it like any other formula  -- or even refresh it somehow.  

The other thing I tried is referencing an adjacent cell:  As you noted above, your parameter in cell A5 came as


I tried to figure out a way to change it to reference the adjacent cell (B5) so that the filtering parameter could remain constant and have the variable change in B5 but that no success.  I think I understand why you created a macro -- you would think that Microsoft would have at least come up with a way to refresh it.  :P

Thanks again.
Steve_BradyAuthor Commented:
By the way, how can I move your macro to my spreadsheet?  Can it just be copy/pasted somehow?  Also, how do I apply it to my filtering column (which is not Column A)?
Hi Steve,

to copy the macro you can go to the Visual Basic editor in your table like described above.
Insert a new module with the menu "Insert" and "Module" at the top menu bar. An empty module window will be opened. Copy and paste the following text into the empty window (if an "Option Explicit" appears, paste it at the bottom of this line):

Public Const cnTableRange = "A8:A14"
Public Const cnFilterRange = "A4:A5"

Sub SetFilter()
    Range(cnTableRange).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(cnFilterRange), Unique:=False
End Sub

I changed it here a little bit to use constants instead of literals, so you only need to change the values "A8:A14" to fit to your complete table range containing the data (complete if you want to use different advanced filters in different columns, otherwise the column range you want to filter) and "A4:A5" which is the criteria range in your table.
To have a button to start this macro go to the developer tab after you activated it like described above and choose "Insert", a little graphic menu appears with a lot form elements, the button element is the first one in the upper left corner. The mouse cursor changes to a little cross. Click in the sheet where you want to have the button appeared, hold down the left mouse button and drag a rectangle. The button appears with a standard text and a dialog which asks which macro do you want to start. Choose the "SetFilter" macro from the list and click OK. A frame around the button is still open, click on the text in the button and change it like you want. Then click outside once and your button is ready to work. You can change the button text and position at any time later by clicking it with the right mouse button.

Yes, you're right: If you change the filter parameter in the filter criteria range of the sheet nothing happens. That's what I meant with annoying that you normally must open the advanced filter dialog again and enter the ranges again. The macro/button solution above solves this problem: Change the criteria, click the button, that's it.
Advantage against the auto filter is that you can always see which filters will be applied, in the autofilter you must check each dropdown. Another advantage is that you can save the criteria, by simply copying the criteria range i.e. to another sheet or whatever. And the criteria can be much more complicate than what is possible with the auto filter.

To see further filtering, look at the attached filter demo.
For a lot more examples, look in the help file or online here:




It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.