Hide rows based on a list value

[grahamm]

Hi,

I'm a complete rookie to non basic excel so please excuse that. I'm trying to organise a database of text information and what I would like to do is have a drop down list with a number of topics (e.g Characterisation, Treatment) which relate to information across a number of columns (so title of the paper, key points, reference info etc). So when I select Treatment on the list all of the relevant information will be unhidden in rows 5-30 and if it is not selected those rows are hidden. Then if I select Characterisation rows 31-50 are unhidden and all other rows are hidden so I can only see the information relating to that topic.

I have my drop down list and I understand I need to write a macro in order to hide/unhide the rows based on this list. So say the list is in A2 and all of my information is in rows 3-100 - I need my macro to hide/unhide those rows based on what is selected from the drop down list. Hopefully my explanation was clear enough.

If you can please explain the macro and how to implement it that would be most useful. I'd rather learn how to use the macro so I can edit it in future rather than just copying and pasting something. Thanks in advance for any advice.

Graham

[walruseggman]

You don't need a macro to do this, and it's probably easier, too. Just have a column which contains the label you want for each row (Characterization, Treatment, etc.), Then turn on a filter for that column (Data -> Filter), then using the drop down for that column, select the category (or catergories) you want to view.

A macro can be written for this functionality, but since you're already talking about having a listbox, writing code seems a bit of overkill.

[grahamm]

That does sound like a better method. Do you mind explaining a little more on how to implement that because I've played around with the advanced filter but cant seem to achieve the result I want. Is it better to go about it the same way I described in my post or in another way?

Thanks for your help.

[walruseggman]

Right. So you don't need to use the advanced filter, just the regular filter.

Like I said, you're going to need a column that's explicitly for the category. Let's say you choose column D (but can be whatever blank column you want). So from your example, in rows 5 through 30 you will enter the word "Treatment" in column D. Rows 31-50 in column D you'll enter "Characterization" etc. etc. etc.

Now click the top cell in that column, Data -> Filter, then click the little drop-down arrow in that cell. Viola, you can show/ hide whatever catergories you want by clicking the check boxes.