Worksheet change to hide rows based on cell value

M

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
I am trying to work on a worksheet change event.

Here is what i got

Set rng = Range("F37")

Range("A21" & numRows":A30").Select
Selection.EntireRow.Hidden = True

What I want is that every time the value of cell f37 changes I want to hide the rows that are in the range.

Cell f37 will always have a number in it from 0-10

The range I want to hide is A21:A30.

If the vaule in F37 is 0, I want to hide A21:A30
If the value in F37 is 1, I want to hide A22:A30
If the value in F37 is 2, I want to hide A23:A30
and so on so that
If the value in F37 is 9, I want to hide A430:a30
If the value in F37 is 10, I won't want to hide any rows

Any suggestions on how to do it.

Mark
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
it is being manually changed. There is data valadation that only allows 0-10 to be entered.

Mark
 
Upvote 0
This is completely hard-coded, but works for me:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRows As Range, ViewRows As Range

If Intersect([F37], Target) Is Nothing Then Exit Sub

Select Case [F37].Value
    Case Is = 0
        Set HideRows = Rows("21:30")
        Set ViewRows = Nothing
    Case Is = 1
        Set HideRows = Rows("22:30")
        Set ViewRows = Rows("21:21")
    Case Is = 2
        Set HideRows = Rows("23:30")
        Set ViewRows = Rows("21:22")
    Case Is = 3
        Set HideRows = Rows("24:30")
        Set ViewRows = Rows("21:23")
    Case Is = 4
        Set HideRows = Rows("25:30")
        Set ViewRows = Rows("21:24")
    Case Is = 5
        Set HideRows = Rows("26:30")
        Set ViewRows = Rows("21:25")
    Case Is = 6
        Set HideRows = Rows("27:30")
        Set ViewRows = Rows("21:26")
    Case Is = 7
        Set HideRows = Rows("28:30")
        Set ViewRows = Rows("21:27")
    Case Is = 8
        Set HideRows = Rows("29:30")
        Set ViewRows = Rows("21:28")
    Case Is = 9
        Set HideRows = Rows("30:30")
        Set ViewRows = Rows("21:29")
    Case Is = 10
        Set HideRows = Nothing
        Set ViewRows = Rows("21:30")
End Select

On Error Resume Next
HideRows.Hidden = True
ViewRows.Hidden = False

End Sub
 
Upvote 0
Thanks for the reply, it does just what I wanted it to do.

Now I would like to know how to have two different worksheet change evens on the same sheet. Along with the code from above, I also have some more code that copies certain cells, and pastes them. Both codes work if they are kept seperate, but as they are for the same sheet they need to be togther, right?

Here are the codes, what do I have to do to make them both work?

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim HideRows As Range, ViewRows As Range
Dim rng As Range

UnprotectSheets

If Intersect([F38], Target) Is Nothing Then Exit Sub

Select Case [F38].Value
    Case Is = 0
        Set HideRows = Rows("21:30")
        Set ViewRows = Nothing
    Case Is = 1
        Set HideRows = Rows("22:30")
        Set ViewRows = Rows("21:21")
    Case Is = 2
        Set HideRows = Rows("23:30")
        Set ViewRows = Rows("21:22")
    Case Is = 3
        Set HideRows = Rows("24:30")
        Set ViewRows = Rows("21:23")
    Case Is = 4
        Set HideRows = Rows("25:30")
        Set ViewRows = Rows("21:24")
    Case Is = 5
        Set HideRows = Rows("26:30")
        Set ViewRows = Rows("21:25")
    Case Is = 6
        Set HideRows = Rows("27:30")
        Set ViewRows = Rows("21:26")
    Case Is = 7
        Set HideRows = Rows("28:30")
        Set ViewRows = Rows("21:27")
    Case Is = 8
        Set HideRows = Rows("29:30")
        Set ViewRows = Rows("21:28")
    Case Is = 9
        Set HideRows = Rows("30:30")
        Set ViewRows = Rows("21:29")
    Case Is = 10
        Set HideRows = Nothing
        Set ViewRows = Rows("21:30")
End Select

On Error Resume Next
HideRows.Hidden = True
ViewRows.Hidden = False


Set rng = Range("b11:b20, i11:i20, a21:i30, c32")

If Not Intersect(Target, rng) Is Nothing Then
    Intersect(Target, rng).Offset(48, 0).Value = Intersect(Target, rng).Value
End If


End Sub

Thanks, Mark
 
Upvote 0
If the code that copies cells is a Worksheet_Change code, then yes, you will have to get them to play together nicely in the one sub.

Assuming the code you posted above is all of the code you want to be in the Worksheet_Change macro, try this. Instead of exiting the macro if F38 was not the changed cell, it will skip the Select Case statements and continue after the IF statement:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRows As Range, ViewRows As Range
Dim rng As Range

Application.ScreenUpdating = False

'if more than 1 cell was changed, do nothing
If Target.Count > 1 Then Exit Sub

'if F38 was the cell that was changed, hide/unhide rows
If Not Intersect([F38], Target) Is Nothing Then
    Select Case [F38].Value
        Case Is = 0
            Set HideRows = Rows("21:30")
            Set ViewRows = Nothing
        Case Is = 1
            Set HideRows = Rows("22:30")
            Set ViewRows = Rows("21:21")
        Case Is = 2
            Set HideRows = Rows("23:30")
            Set ViewRows = Rows("21:22")
        Case Is = 3
            Set HideRows = Rows("24:30")
            Set ViewRows = Rows("21:23")
        Case Is = 4
            Set HideRows = Rows("25:30")
            Set ViewRows = Rows("21:24")
        Case Is = 5
            Set HideRows = Rows("26:30")
            Set ViewRows = Rows("21:25")
        Case Is = 6
            Set HideRows = Rows("27:30")
            Set ViewRows = Rows("21:26")
        Case Is = 7
            Set HideRows = Rows("28:30")
            Set ViewRows = Rows("21:27")
        Case Is = 8
            Set HideRows = Rows("29:30")
            Set ViewRows = Rows("21:28")
        Case Is = 9
            Set HideRows = Rows("30:30")
            Set ViewRows = Rows("21:29")
        Case Is = 10
            Set HideRows = Nothing
            Set ViewRows = Rows("21:30")
    End Select
    On Error Resume Next
    HideRows.Hidden = True
    ViewRows.Hidden = False
End If

'if F38 was *not* the changed cell, code continues here

Application.EnableEvents = False

Set rng = Range("B11:B20, I11:I20, A21:I30, C32")

If Not Intersect(Target, rng) Is Nothing Then
    Target.Offset(48, 0).Value = Target.Value
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Von Pookie said:
If the code that copies cells is a Worksheet_Change code, then yes, you will have to get them to play together nicely in the one sub.

Assuming the code you posted above is all of the code you want to be in the Worksheet_Change macro, try this. Instead of exiting the macro if F38 was not the changed cell, it will skip the Select Case statements and continue after the IF statement:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRows As Range, ViewRows As Range
Dim rng As Range

Application.ScreenUpdating = False

'if more than 1 cell was changed, do nothing
If Target.Count > 1 Then Exit Sub

'if F38 was the cell that was changed, hide/unhide rows
If Not Intersect([F38], Target) Is Nothing Then
    Select Case [F38].Value
        Case Is = 0
            Set HideRows = Rows("21:30")
            Set ViewRows = Nothing
        Case Is = 1
            Set HideRows = Rows("22:30")
            Set ViewRows = Rows("21:21")
        Case Is = 2
            Set HideRows = Rows("23:30")
            Set ViewRows = Rows("21:22")
        Case Is = 3
            Set HideRows = Rows("24:30")
            Set ViewRows = Rows("21:23")
        Case Is = 4
            Set HideRows = Rows("25:30")
            Set ViewRows = Rows("21:24")
        Case Is = 5
            Set HideRows = Rows("26:30")
            Set ViewRows = Rows("21:25")
        Case Is = 6
            Set HideRows = Rows("27:30")
            Set ViewRows = Rows("21:26")
        Case Is = 7
            Set HideRows = Rows("28:30")
            Set ViewRows = Rows("21:27")
        Case Is = 8
            Set HideRows = Rows("29:30")
            Set ViewRows = Rows("21:28")
        Case Is = 9
            Set HideRows = Rows("30:30")
            Set ViewRows = Rows("21:29")
        Case Is = 10
            Set HideRows = Nothing
            Set ViewRows = Rows("21:30")
    End Select
    On Error Resume Next
    HideRows.Hidden = True
    ViewRows.Hidden = False
End If

'if F38 was *not* the changed cell, code continues here

Application.EnableEvents = False

Set rng = Range("B11:B20, I11:I20, A21:I30, C32")

If Not Intersect(Target, rng) Is Nothing Then
    Target.Offset(48, 0).Value = Target.Value
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
Click to expand...


Hi.
Is it possible that we can have changes in another sheet of same workbook.
For example, if we have sheet 1 and select cases as : if ("A1") =1 , then rows from B to D in Sheet 2 are hidden ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Hiding rows based on cell value
Replies
2
Views
59
smozgur
smozgur
E
  • Question
Hide rows based on prior setup sheet
2
Replies
10
Views
202
edjohn20
E
PuntingJawa
  • Solved
Macro to hide/unhide rows based on cell value
2
Replies
11
Views
131
PuntingJawa
PuntingJawa
David Kemp
  • Solved
Hide different columns depending on value in a cell
Replies
5
Views
100
Peter_SSs
Peter_SSs
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
371
kevin9999
K

Forum statistics

Threads
1,214,409
Messages
6,119,339
Members
448,888
Latest member
Arle8907

Share this page

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