VBA Hide / Unhide Rows Based on Dropdown Box (Excel 2010)

[BenGee]

Hi all,

In cell A1 I have a dropdown list which you can select either "LTM", "Retail" or "Retail Pre-Sale". Also, between cell A10 - A50 contains either "LTM", "Retail" or "Retail Pre-Sale" (there is a range of data in adjacent columns but this is a mute point).

What I'm looking to do is have a worksheet change event macro that hides all rows between 10 - 50 except what's been selected from the dropdown. E.g. I select "LTM" from the dropdown and all the rows that do not have "LTM" in column A (between rows 10 to 50) will auto-hide. Subsequently, if the dropdown box is empty then all rows are visible.

Anyone have any suggestions, or, point me in the direction of something I can adapt?

Thanks in advance for your help
Ben

 

[My Aswer Is This]

I looked at your post and wanted to help but not sure how to deal with two contradicting situations.

You say if cell value does not equal Range("A1").value do this.
But then say if cell value does equal Range("A1").value do this.


So if column A value does not have "George" then hide row.
So if column A value does have Nothing then unhide row

These are two contradicting situations.

 

[BenGee]

Thanks for having a look and replying.

I see what you mean' The contradicting situations didn't even cross my mind.

I don't need to have the 'does equal range then unhide row' statement - I could write something seperately to unhide all rows before closing.

It's more important that I have the 'does not have value then hide rows' statement. Any ideas would be massively appreciated!

Thanks for your help so far

 

[My Aswer Is This]

Try this:
I added a feature to also unhide all rows between 10 to 50
If you enter the value "Unhide" into Range ("A2") all rows between 10 to 50 will be unhidden

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Range("A1").Value
Dim c As Range
    For Each c In Range("A10:A50")
    If c.Value <> ans Then c.Rows.Hidden = True
    Next
    
End If
If Not Intersect(Target, Range("A2")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "Unhide" Then Rows("10:50").Hidden = False
Range("A2").ClearContents
End If
End Sub

 

[BenGee]

This is perfect, really appreciate your help.

One last thing, I realized that I asked for the wrong thing and have made some changes to my worksheet so have adapted your code a little to get what I'm looking for;

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Range("B3").Value
Dim c As Range
    For Each c In Range("A5:A100")
    If c.Value = ans Then c.Rows.Hidden = False
    Next
   
End If
If Not Intersect(Target, Range("A2")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "Unhide" Then Rows("10:50").Hidden = False
Range("A2").ClearContents
End If
End Sub

However now, if I select 'LTM' then all rows with 'LTM' in column A (within the above range) will unhide as requested (perfect). But, if I then go onto select 'Retail' it will simply unhide rows with Retail in column A (within the above range) however doesn't then hide rows with LTM - So in effect all rows with LTM & Retail will be visible. Any idea how I can get around this?

Thanks for all your help so far

 

[My Aswer Is This]

I'm getting a little confused. You original post said:

E.g. I select "LTM" from the dropdown and all the rows that do not have "LTM" in column A (between rows 10 to 50) will auto-hide.

But now I see you have changed the script to say:
If c.Value = ans Then c.Rows.Hidden = False

So now since you have "=" instead of "<>"

It's doing just the opposite of what you asked for.

So if you choose "LTM" what do you want the script to do.

And you have now changed the range from
A10:A50
To
A5:A100

so now you will have to change the unhide part of the other script:

If Target.Value = "Unhide" Then Rows("5:100").Hidden = False


So let me know what you want because I'm confused.

I am glad to see your trying to read scripts and modify then to your liking.

=means equal
<> means does not equal

 

[My Aswer Is This]

If you want to play around with the script just remember to read the if statement

if ans = or <>

hidden=true
or hidden = False

These things should be understood so you can learn more about scripting

 

[BenGee]

Thank you for replying

Sorry for the confusion, I've made changes to my workbook since my original post and also realize that my original post is miss-leading.

What I'm looking for is;

1) Rows 5:100 are hidden (I've already got code that hides these rows before closing - so when someone opens the workbook these area already hidden)
2) When the user selects from a dropdown box within "B3"; either "LTM", "Retail" or "Retail Pre-Sale"
3) Then it will check for matching text between A5:A100 and unhide all corresponding rows that contain a match within column A
4) If I were to change the selection within "B3" (e.g. From "LTM" to "Retail", then it will hide the previous selection and carry out parts 2 + 3 (above) and unhide all rows that contain "Retail" within column A

Hope I've been a bit clearer, and, thanks again for all your help.

Also - Thanks for the extra info around reading script, doing my best to learn to it

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim ans As String
ans = Range("B3").Value
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("5:100").Hidden = True
    For i = 5 To 100
        If Cells(i, "A").Value = ans Then Rows(i).Hidden = False
    Next
End If
Application.ScreenUpdating = True
End Sub

 

[BenGee]

That worked perfectly, thank you so much for you help