VBA Assistance to Hide Rows based on values

svickers1972

New Member
Joined
Feb 14, 2012
Messages
5
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!
 
Last edited:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Feb 14, 2012
Messages
5
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

Legend
Joined
May 15, 2003
Messages
29,536
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.

Code:
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

New Member
Joined
Feb 14, 2012
Messages
5

ADVERTISEMENT

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.

Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Feb 14, 2012
Messages
5

ADVERTISEMENT

So that part stumped me...:oops:

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?

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("$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")
 
Last edited by a moderator:

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Feb 14, 2012
Messages
5
I am trying to get the hang of the posting and "quoting" my scripts...sorry for the cumbersome posts.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
No problem, you were just missing the [ code] tag at the beginning.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top