VBA Code to Hide Rows Dependent on Selection?

thelostscott

Board Regular
Joined
May 7, 2010
Messages
226
Hi all,

I'm trying to get a worksheet to hide certain rows depending on the selection of a DV list. What I have so far is this:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
 
    On Error Resume Next
 
        If Range("C2") = "Option 1" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("3:100").EntireRow.Hidden = False
        End If
 
        If Range("C2") = "Option 2" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("201:298").EntireRow.Hidden = False
        End If
 
        If Range("C2") = "Option 3" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("300:397").EntireRow.Hidden = False
        End If
 
        If Range("C2") = "Option 4" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("102:199").EntireRow.Hidden = False
        End If
 
    On Error GoTo 0
 
Application.ScreenUpdating = True
End Sub

It seems to work ok, however i'm not sure whether a "Worksheet_Change" event is the best way to go as every time I enter data into the tables it re-runs the Macro. How do I get it to only run the Macro when a selection is made in "C2" (apart from adding a button click)?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) <> "C2" Then Exit Sub
Application.ScreenUpdating = False
 
    On Error Resume Next
 

thelostscott

Board Regular
Joined
May 7, 2010
Messages
226
I just tried that bit of code and it doesn't hide any of the rows anymore? It does however stop running the Macro when selecting any other cell, which is brilliant.

I have changed my code so that it now looks like this:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
If Target.Address(False, False) <> "C2" Then Exit Sub
 
Application.ScreenUpdating = False
 
    On Error Resume Next
 
 
        If Range("C2") = "Option 1" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("3:100").EntireRow.Hidden = False
        End If
 
        If Range("C2") = "Option 2" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("201:298").EntireRow.Hidden = False
        End If
 
        If Range("C2") = "Option 3" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("300:397").EntireRow.Hidden = False
        End If
 
        If Range("C2") = "Option 4" Then
        Rows("3:400").EntireRow.Hidden = True
        Rows("102:199").EntireRow.Hidden = False
        End If
 
    On Error GoTo 0
 
Application.ScreenUpdating = True
 
 
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I don't see why that change would stop it from working. Perhaps like this

Rich (BB code):
If Target.Value = "Option 1" Then
 

thelostscott

Board Regular
Joined
May 7, 2010
Messages
226

ADVERTISEMENT

Ahhh yes, thanks for that!
 

thelostscott

Board Regular
Joined
May 7, 2010
Messages
226
Sorry, it seems to still be acting up. I had got it working again by using:


Code:
If Range ("C2") = "Option 1" Then

But what happens now is when I choose an "Option" from the DV List, nothing happens. The Macro doesn't run at all and it remains on the previous "Option".

However, if I then go and select another cell, then go back and re-select the DV List (or "C2") again, it runs the Macro and unhides the rows I need it to???
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Ah, you are using the wrong event. It should be

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 

thelostscott

Board Regular
Joined
May 7, 2010
Messages
226
Nailed it. That solved the issue, always something simple... Thanks again VoG!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,724
Members
416,130
Latest member
galgozzi

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