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

My Aswer Is This · Aug 8, 2019

Not sure what this means:

This is what I thought initially, but have added additional options and don't know how to hide non sequential row ranges.

Give me a example of

non sequential row ranges

AWarry · Aug 8, 2019

My Aswer Is This said:
Not sure what this means:

This is what I thought initially, but have added additional options and don't know how to hide non sequential row ranges.

Give me a example of

non sequential row ranges

If DVA Initial (Rows 36:50 visible and Rows 80:84 visible ) or (Rows 52:66 and Rows 85:88 not visible)

My Aswer Is This · Aug 8, 2019

I do not understand:

non sequential in my thinking means something like this:

1
4
6
8
12

[h=2]Definition of non sequential[/h]

not relating to, arranged in, or following a sequence : not sequential a nonsequential narrative style a nonsequential list of serial numbers.

_{^{<strike>

</strike>}}

My Aswer Is This · Aug 8, 2019

Here is how I would do it:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
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

My Aswer Is This · Aug 8, 2019

To Hide a lot of

non sequential

rows you can use something like this:

Code:

Range("10:10,14:14,5:5,17:17").EntireRow.Hidden = False

This would hide row 10 row 14 row 5 and row 17

AWarry · Aug 8, 2019

Yes, non sequential but instead of 1,4,6,8,12... more like 1, 2, 3, 7, 8, 9... 1,2,3 is one group and 7,89 another group.. but both groups are not sequential (ie 1, 2,3,4,5,6)

My Aswer Is This · Aug 8, 2019

So give me a exact example.

Like if Target.value="Alpha"

Then hide these rows

And I will show you how to do it.

AWarry · Aug 8, 2019

Exact example

If Target.value="DVA Initial"
Then hide these rows (85 to 88) and (52:66)

My Aswer Is This · Aug 8, 2019

Like this:

Code:

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

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

AWarry

New Member

Excel Facts

My Aswer Is This

Well-known Member

AWarry

New Member

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

AWarry

New Member

My Aswer Is This

Well-known Member

AWarry

New 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

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

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