how to add next vba code to this one?

[mychi11]

I am new to vba. My previous two vba codes are working.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String, Newvalue As String
  
    'On Error GoTo Exitsub
    Application.ScreenUpdating = False
    If Target.Address = "$B$9" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Or Target.Value = "" Then
            Application.EnableEvents = True
            Exit Sub
        Else
            Application.EnableEvents = False
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
                Target.Value = Newvalue
            Else
                Target.Value = Oldvalue & ", " & Newvalue
            End If
        End If
        Application.EnableEvents = True
    Else
        Rows("40:89").EntireRow.Hidden = False
        x = Range("B31").Value
        Select Case x
            Case ""
                Rows("40:89").EntireRow.Hidden = True
            Case 1 To 49
                Rows(40 + x & ":89").EntireRow.Hidden = True
            Case 50
    End Select
    End If
    Application.ScreenUpdating = True
End Sub

I am trying to add this few lines to the current one

y = Range("B5").Value
Select Case y
Case "OC FZ": Rows("12:93").EntireRow.Hidden = True
Case "FET": Rows("27:88").EntireRow.Hidden = True
End Select

Application.ScreenUpdating = True
End Sub

can anyone please help ? Thank you in advance

 

[Dan_W]

It solely depends on what you want to happen. You have a number of conditions in this code, and where and how this additional code should be implemented will depend entirely on which condition should priority over which other condition.

As it reads, this code is triggered if there is a change to the content of any of the cells on the worksheet. There are two top-level possibilities - either the change was made to: (1) Cell B9; or (2) any other cell. I suspect that you want this code under the second head - which focuses on hiding/unhiding certains rows - principally rows 40-89 - depending on what is in cell B31. Your new code purports to hide/unhide rows: (a) 19 to 93; or (b) 27 to 88 - depending on cell B5.

But the question then becomes - what should happen if there are values in cells B5 and B31 which overlap or conflict. So, what do you want to happen -for example - if B31 has the value 30 (resulting in rows 40:69 being visible and rows 70:89 being hidden) and cell B5 has FET (resulting in rows 27:88 being hidden)? If B31 takes priority, then rows 12 to 89 will be hidden except for rows 40 to 69, which will be visible. If B5 takes priority, then rows 27 to 88 (and potentially 89?) will be hidden.

There's a few other possible permutations, but I won't waste your time listing them all - it's probably a very easy adjustment to make, but we just need a bit more information.

 

[mychi11]

Thank you for your reply. I have figured out a way where the values will not overlap.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String, Newvalue As String

'On Error GoTo Exitsub
Application.ScreenUpdating = False
If Target.Address = "$B$9" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Or Target.Value = "" Then
Application.EnableEvents = True
Exit Sub
Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
Application.EnableEvents = True
Else
Rows("11:112").EntireRow.Hidden = False
x = Range("I31").Value

Select Case x
Case "FET"
Rows("11:91").EntireRow.Hidden = True
Case "OC FZ"
Rows("11:28").EntireRow.Hidden = True
Rows("33:95").EntireRow.Hidden = True
Case ""
Rows("40:89").EntireRow.Hidden = True
Case 1 To 49
Rows(40 + x & ":89").EntireRow.Hidden = True
Case 50

End Select
End If
Application.ScreenUpdating = True
End Sub

But i need another value y.
y = Range("B5").Value

Select Case y
Case "OC THW"
Rows("11:28").EntireRow.Hidden = True

Is there a way to add these few lines to the existing vba code?

 

[Dan_W]

I'm still not clear on what you're actually doing or what you actually want, but purely as a shot-in-the dark, try the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String, Newvalue As String

'On Error GoTo Exitsub
Application.ScreenUpdating = False

If Target.address = "$B$9" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Or Target.Value = "" Then
        Application.EnableEvents = True
        Exit Sub
    Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
    Application.EnableEvents = True
Else
    Rows("11:112").EntireRow.Hidden = False
    x = Range("I31").Value
    Select Case x
        Case "FET"
            Rows("11:91").EntireRow.Hidden = True
        Case "OC FZ"
            Rows("11:28").EntireRow.Hidden = True
            Rows("33:95").EntireRow.Hidden = True
        Case ""
            Rows("40:89").EntireRow.Hidden = True
        Case 1 To 49
            Rows(40 + x & ":89").EntireRow.Hidden = True
        Case 50
    End Select
    y = Range("B5").Value
    If y = "OC THW" Then Rows("11:28").EntireRow.Hidden = True
End If

Application.ScreenUpdating = True

End Sub

 

[mychi11]

thank you so much. This is exactly the solution I am looking for. Sorry for not able to express what I want well.