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)?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
I don't see why that change would stop it from working. Perhaps like this

Rich (BB code):
If Target.Value = "Option 1" Then
 
Upvote 0
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???
 
Upvote 0
Ah, you are using the wrong event. It should be

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top