Hide row based on cell value and cell text.

[mychi11]

I have the following code where the cell equal to 1,2,3,4 and etc where the cell 54 to 103 should collapse accordingly. My code is as follow
Private Sub Worksheet_Change(ByVal Target As Range)
Rows("54:103").EntireRow.Hidden = False
x = Range("A29").Value
Select Case x
Case ""
Rows("54:103").EntireRow.Hidden = True
Case 1 To 49
Rows(54 + x & ":103").EntireRow.Hidden = True
Case 50
Rows("103:104").EntireRow.Hidden = False
End Select
End If
Application.ScreenUpdating = True
End Sub
I need a subsequent code where cell B9="APPLE", row 5 till 20 and row 27:100 should collpase
and when B9="Orange", row 3 till 20 and row 30-50 should collapse

 

[FTSCH]

Is your data in one contiguous table? If so, have you tried using Data-Sort-Filter?

 

[mychi11]

nope I am actually trying to create a form. I want the row to be dynamic so that it is easier for me to input the data based on conditions set

 

[mychi11]

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("38:87").EntireRow.Hidden = False
x = Range("A29").Value
Select Case x
Case ""
Rows("38:87").EntireRow.Hidden = True
Case 1 To 49
Rows(38 + x & ":87").EntireRow.Hidden = True
Case 50
Rows("12:108").EntireRow.Hidden = False
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
End If
Application.ScreenUpdating = True
End Sub

I tried this code, there is an error end if without block if. Wonder if anyone can help

 

[FTSCH]

Not clear on exactly what the flow of the code is doing, I broke your code up into blocks of If, Then, Else statements (you can move lines in and out as you see fit to test). Without spending much time on this, there are several wholes in the code, but I'm guessing (at your logic) of matching the If with its End If structure. Not sure what your doing with the 2 select statements and if you can combine the two? A Data Table with it's endless sorting and filtering features (hiding rows in the table based on selected values) would simplify your attempt enormously.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String, Newvalue As String
Dim x As String, y 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
End If
Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
End If

If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If

Application.EnableEvents = True

'Else

Rows("38:87").EntireRow.Hidden = False
x = Range("A29").Value

Select Case x
Case 1 To 20
Rows("38:87").EntireRow.Hidden = True
Case 1 To 49
Rows(38 + x & ":87").EntireRow.Hidden = True
Case 50
Rows("12:108").EntireRow.Hidden = False
End Select

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