Use VBA to hide non sequential range of rows based on the cell value

AWarry · Aug 8, 2019

Hi

I am trying to create a form where there is a drop down boxes for selection of 4 options (cell G12).
This is what I would like the programming to do:

Cell G12 there are 4 selections now

If blank Rows 36:66 hidden True Rows 80:88 hidden True

If DVA Initial Rows 36:50 visible True Rows 52:66 visible False Rows 80:84 visible True Rows 85:88 visible False

If DVA Review Rows 36:50 visible False Rows 52:66 visible True Rows 80:84 visible True Rows 85:88 visible False
If Private/Other initial Rows 36:50 visible True Rows 52:66 visible False Rows 80:84 visible False Rows 85:88 visible True

If Private/Other Review Rows 36:50 visible False Rows 52:66 visible True Rows 80:84 visible True Rows 85:88 visible False

Not entirely sure that coding instruction is correct so basically when DVA Initial or Review is selected Rows 80:84 are visible and 85:88 are hidden
Private/Other Rows 80:84 hidden and 85:88 visible

Either DVA or Private Initial then Rows 36:51 visible and 52:66 hidden
Either DVA or Private Review Rows 36:51 hidden and 52:66 visible

It would be also very good if I can have a second drop down option in the same sheet with following instructions.

Cell R13 yes or no
If blank Rows 14:20 hidden

If yes Rows 14: 20 hidden
If no Rows 14:20 visible

This is what I thought initially, but have added additional options and don't know how to hide non sequential row ranges.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("f13"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "DVA Review": Rows("23:38").EntireRow.Hidden = True
Rows("39:53").EntireRow.Hidden = False
Case Is = "DVA Initial": Rows("23:38").EntireRow.Hidden = False
Rows("39:53").EntireRow.Hidden = True
Case Is = "Private/Other Initial": Rows("23:38").EntireRow.Hidden = False
Rows("39:53").EntireRow.Hidden = True
Case Is = "Private/Other Review": Rows("23:38").EntireRow.Hidden = False
Rows("39:53").EntireRow.Hidden = True

End Select
End If

End Sub

AWarry · Aug 8, 2019

This seemed to work.. I have been successful (thanks to your help) in getting the first drop down box selections to hide and unhide.

However when I attempt to duplicate for a second drop down box selection (yes and no response) it is coming up with a ambigious name Worksheet_Change. do I need to have unique names for each section? error

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g12"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "DVA Review": Range("85:88,36:51").EntireRow.Hidden = True
Range("80:84,52:66").EntireRow.Hidden = False
Case Is = "DVA Initial": Range("80:84,36:51").EntireRow.Hidden = False
Range("85:88,52:66").EntireRow.Hidden = True
Case Is = "Private/Other Initial": Range("85:88,36:51").EntireRow.Hidden = False
Range("80:84,52:66").EntireRow.Hidden = True
Case Is = "Private/Other Review": Range("85:88,52:66").EntireRow.Hidden = False
Range("80:84,36:51").EntireRow.Hidden = True
End Select
End If

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g12"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Yes": Rows("14:20").EntireRow.Hidden = True

Case Is = "No": Rows("14:20").EntireRow.Hidden = False

End Select
End If

End Sub

My Aswer Is This · Aug 8, 2019

Not sure why I'm providing you with code but then your showing code the way you want it written.

The code in your last post is not what I just provided.

Why don't you like the code the way I wrote it.

My Aswer Is This · Aug 8, 2019

It would be like this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/8/2019  10:53:43 PM  EDT
If Not Application.Intersect(Range("F13"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
    Case Is = "DVA Review"
        Rows("23:38").EntireRow.Hidden = True
        Rows("39:53").EntireRow.Hidden = False
    Case Is = "DVA Initial"
        Rows("23:38").EntireRow.Hidden = False
        Rows("39:53").EntireRow.Hidden = True
        Rows("85:88").EntireRow.Hidden = True
        Rows("52:66").EntireRow.Hidden = True
    Case Is = "Private/Other Initial"
        Rows("23:38").EntireRow.Hidden = False
        Rows("39:53").EntireRow.Hidden = True
    Case Is = "Private/Other Review"
        Rows("23:38").EntireRow.Hidden = False
        Rows("39:53").EntireRow.Hidden = True
End Select
End If

If Target.Address = Range("G12").Address Then
Select Case Target.Value
    Case Is = "Yes"
    Rows("14:20").EntireRow.Hidden = True
    Case Is = "No"
        Rows("14:20").EntireRow.Hidden = False
End Select
End If
End Sub

Use VBA to hide non sequential range of rows based on the cell value

AWarry

New Member

AWarry

New Member

Excel Facts

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Use VBA to hide non sequential range of rows based on the cell value

AWarry

New Member

AWarry

New Member

Excel Facts

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock