VBA Assistance to Hide Rows based on values

[svickers1972]

I am new to posting here, so please pardon any newbie statements or questions!

So, I have seen several recommendations for VBA code to Hide Rows with VB based on a cell value, but it seems that none are exactly what I am looking for, any help would be greatly appreciated.

On our spreadsheet in cell A7, A9, and A11 I have a drop down selection for the user to choose their view. Starting in Cell A13:S23 is for the first grouping (referencing from the A7 selection). A27:S88 is for the A7 Drop Down reference and A90:S282 is for the A11 drop down selection.

S:S has a If,then that is controlled by the drop down to yield 1 or 0...

What we are looking to do is hide any row that S=0 and show if S=1, also as a secondary hide if C13:C282 = "" then hide the row as well. We are looking to perform these tasks on selection of each A7, A9 and A11 without manually having the user to call the macro.

To add to the code after each selection we would like it to also sort each section of the selected fields by 'C' A13:S23, A27:S88, A90:S282 would be individually sorted by the value in 'C'.

Thank you for any assistance that can be provided!

 

[Smitty]

Welcome to the Board!

You can use a change event, which will repond to a data validation selection.

Here's some boilerplate change event code:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

The specific code that you need for hiding/unhiding & sorting you can generate with the macro recorder, then add it to the change event. Feel free to post back what you get if you need some help with it.

HTH,

 

[svickers1972]

Thank you Smitty for the rapid response!

As I am very "greeen" with VB, I will need some additional feedback as to how to make the necessary changes to your below code and implemenation.

Thanks!

Welcome to the Board!

You can use a change event, which will repond to a data validation selection.

Here's some boilerplate change event code:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("xxx")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)

End Sub


The specific code that you need for hiding/unhiding & sorting you can generate with the macro recorder, then add it to the change event. Feel free to post back what you get if you need some help with it.

HTH,

 

[Smitty]

Record a macro, or a series of macros hiding/unhiding the ranges based on your specifications. Then you have the basis for the code that you can run in the change event.

From there you need to indentify the steps in which Excel should analyze changes.

E.G.

If Target.Address = $A$7 and Target.Value = 1 Then
  Do something
Else
  Do something else
End If

And so on.

You can also look at using AutoFilter to hide certain rows based on their values. That is also easily recordable.

 

[svickers1972]

So here is the code that I have so far:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("$A$7")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
With Me.Range("s:s")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=1
End With
'Sort Function
Worksheets("Site SPLASH").Range("B13:s23").Sort _
Key1:=Worksheets("Site SPLASH").Range("C12"), Order1:=xlDescending

End Sub

Just a couple of things that seem to be missing...it appears that I must "click" back into the cell "A7" to activate the change. Also, If in the 'C' range, it continues to show even if there is a "" or a 0 value.

Can you assist from this point or provide feedback on what you have provided?

Thank you for your time and attention!

Record a macro, or a series of macros hiding/unhiding the ranges based on your specifications. Then you have the basis for the code that you can run in the change event.

From there you need to indentify the steps in which Excel should analyze changes.

E.G.

If Target.Address = $A$7 and Target.Value = 1 Then
  Do something
Else
  Do something else
End If

And so on.

You can also look at using AutoFilter to hide certain rows based on their values. That is also easily recordable.

 

[Smitty]

Don't use SelectionChange, but Change. SelectionChange fires when you move from cell to cell, while Change fires when you change a cell.

To include the C range you need to add it to the rng argument:

Set rng = Target.Parent.Range("$A$7")

 

[svickers1972]

So that part stumped me...

I also seemed to have ran into another issue:
How can I set this with the option of the three selection process? From what I am gathering I can only 'call' the hiding in the VB once and the other selections are non-responsive- Does that make sense?

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("$A$7")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
With Me.Range("s:s")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=1
End With
'Sort Function
Worksheets("Site SPLASH").Range("B13:s23").Sort _
Key1:=Worksheets("Site SPLASH").Range("C12"), Order1:=xlDescending
End Sub
 
Private Sub Worksheet_Change1(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("$A$7")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
With Me.Range("s:s")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=1
End With
'Sort Function
Worksheets("Site SPLASH").Range("B13:s23").Sort _
Key1:=Worksheets("Site SPLASH").Range("C12"), Order1:=xlDescending
 
End Sub
 
Private Sub Worksheet_Change2(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("$A$9")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
With Me.Range("s:s")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=1
End With
'Sort Function
Worksheets("Site SPLASH").Range("B27:s88").Sort _
Key1:=Worksheets("Site SPLASH").Range("C26"), Order1:=xlDescending

Thanks!

Don't use SelectionChange, but Change. SelectionChange fires when you move from cell to cell, while Change fires when you change a cell.

To include the C range you need to add it to the rng argument:

Set rng = Target.Parent.Range("$A$7")

 

[Smitty]

You can only have one change event per sheet, so you need to combine them.

If you come up with your evaluation rules then it's easily doable.

E.G.

If ABC do one thing

If XYZ do another

If 123 do another

But you have to figure out the criteria.

 

[svickers1972]

I am trying to get the hang of the posting and "quoting" my scripts...sorry for the cumbersome posts.

 

[Smitty]

No problem, you were just missing the [ code] tag at the beginning.