Previous actions remain-CodePudding

I am trying to do a user input whereby there are 2 inputs cell A1 and cell B1. For actions of cell A1, my code is as follows:

If target.Address = "$A$1" Then
        Select Case Sheets("Input").Range("A1")
            Case "Hide1"
                Range("A3:A5").EntireRow.Hidden = True
            Case "Hide2"
                Range("A7:A9").EntireRow.Hidden = True
     End Select
End If

For actions of cell A2,my code is as follows:

If target.Address = "$A$2" Then
        Select Case Sheets("Input").Range("A2")
            Case "Hide3"
                Range("A11:A13").EntireRow.Hidden = True
            Case "Hide4"
                Range("A15:A17").EntireRow.Hidden = True
     End Select
End If

How do I make sure that after changing A1, the actions (rows hidden) continue even after I change A2? E.g. I change A1 to "Hide 1", Rows 3-5 gets hidden. Next, I change A2 to "Hide 3", I want Rows 11-13 gets hidden while Rows 3-5 continues to get hidden.

CodePudding user response：

'try this

If target.Address = "$A$1" Then
        Select Case Sheets("Input").Range("A1")
            Case "Hide1"
                Application.EnableEvents=False
                Range("A3:A5").EntireRow.Hidden = True
                Application.EnableEvents=True
            Case "Hide2"
                Application.EnableEvents==False
                Range("A7:A9").EntireRow.Hidden = True
                Application.EnableEvents=True

     End Select
End If

CodePudding user response：

I can't see the problem. After changing A1 to "Hide1" and A2 to "Hide3" rows 3-5 remain hidden. Look perhaps on another part of the code. I can imagine perhaps your code is in SelectionChange event, while it should be in Change event:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Select Case Sheets("Input").Range("A1")
            Case "Hide1"
                Range("A3:A5").EntireRow.Hidden = True
            Case "Hide2"
                Range("A7:A9").EntireRow.Hidden = True
            Case Else
                Range("A3:A5").EntireRow.Hidden = False
                Range("A7:A9").EntireRow.Hidden = False
        End Select
    Else
        If Target.Address = "$A$2" Then
            Select Case Sheets("Input").Range("A2")
                Case "Hide3"
                    Range("A11:A13").EntireRow.Hidden = True
                Case "Hide4"
                    Range("A15:A17").EntireRow.Hidden = True
                Case Else
                    Range("A11:A13").EntireRow.Hidden = False
                    Range("A15:A17").EntireRow.Hidden = False
            End Select
        End If
    End If
    End Sub

CodePudding user response：

Worksheet Change: Hide Rows

This is happening in one worksheet. The code needs to be copied to its module, e.g. Sheet1, Input...etc.
This will monitor changes in cells A1 (values "Hide1" and "Hide2") and A2 (values "Hide3" and "Hide4") and at any time, only one of all four 'row sets' will get hidden.
If both cells were changed, only the first cell's rows will get hidden (see Exit For).
Although the range (urg) represents entire rows, .EntireRow is still necessary when hiding.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim sAddresses As Variant: sAddresses = VBA.Array("A1", "A2")
    Dim uVals As Variant: uVals = VBA.Array("Hide1", "Hide2", "Hide3", "Hide4")
    Dim urg As Range: Set urg = Range("3:5,7:9,11:13,15:17")
    
    Dim sCell As Range
    Dim iCell As Range
    
    Dim n As Long
    
    For n = 0 To UBound(sAddresses)
        Set sCell = Range(sAddresses(n))
        Set iCell = Intersect(sCell, Target)
        If Not iCell Is Nothing Then
            urg.EntireRow.Hidden = False ' unhide all 'row sets'
            Select Case LCase(iCell.Value)
            Case LCase(uVals(n * 2))
                urg.Areas(n * 2   1).EntireRow.Hidden = True
            Case LCase(uVals(n * 2   1))
                urg.Areas(n * 2   2).EntireRow.Hidden = True
            End Select
            ' If both cells are changed, only the first cell's rows get hidden
            Exit For
        End If
    Next n

End Sub