Only show or hide certain rows based on the value in one column using VBA

Hi all,

Thank you for reading this thread.

I am trying to use the commands to show or hide certain rows based on one column's value.

The case here is:

1. I have 10 people on a name list.
2. 10 people have been assigned to 3 groups.
3. Each person is unique and belongs to only one group.
4. Each row has been randomly arranged, followed no A to Z, smallest to largest sequences. For example, Row 2, which is the very next row after the header, will have a name of Michael in Cell A2 and a group number 1 in Cell B2. Then for Row 3, it will have a name of Chris in Cell A3 and a group number 3 in Cell B3.

I want to use below 2 methods to display the results I wanted on this sheet by either using a drop down list, or a Macro button:

Using a drop down list
1. Set up a drop down list with 3 values (group numbers), which of course they will be number 1, 2 and 3, and then with a value to be "ALL".
2. After selected the value to be 1 in the drop down list, only the rows have people belonging to group number 1 will become visible. All other rows, except the header, will disappear/ be hidden.
3. Then, after selected the value to be ALL in the drop down list, all rows will then show again, because I want to see every single row again.

Using a Macro button
1. Set up a Macro button to be named as Group Number.
2. On the right side of this button, on the example sheet I have attached, leave a blank cell, which is F15, for the purpose of entering 4 values: 1, 2, 3, ALL.
3. Then, I want to use this Macro button to have the same result as how I wanted by using the drop down list.
4. For example, I have now entered "2" in F15. Then after I hit the Macro button "Group", it will automatically show every row that has a number 2 value in the B column, and then automatically hide all other rows.
5. The reason why only rows that have a number 2 value need to show, is because I want to see who are the names/ people belong to group number 2, under B column.

I have tried multiple codes but it seems not working at all.

Would highly appreciate if anybody could look into this for me and again, thank you so much!!!

Good morning Winon,

Thank you soooo much for the reply!

The code works perfectly!

I apologize that I might have misinterpreted myself in the original post.

Based on your codes, it seems

1. The Macro button works based on a drop down list selection at A15.
2. If the drop down list is to select "ALL", it will hide all the rows, no matter it is group 1, or 2, or 3.

What I was looking for was:

1. Have a drop down list working as a Macro button.
For example, I was thinking it could possibly work like, if you select number 1 from the drop down list, then this whole sheet will only show rows have number 1 from column B.
In this case, no Macro button is even needed - a drop down list would simply work.

2. If there must be a Macro button to perform such, it should work based on a condition in cell F15, not A15.

I am not sure if this can happen, but again Winon, thank you so much for your help!!!!

Best regards,

Winon, you are an amazing magician!!! Thank you so much and this is exactly what I wanted!!!

Hi Winon, I just added a reputation to your post!

I am re-working some of your codes on another sheet that I might request your help again, so I would prefer not to mark this thread as resolved.

I may have to bother you again...

Hi Winon,

I finally figured out what I needed and have marked this thread as SOLVED.

Thank you very much again and wish you have a wonderful day!