Hide row based on cell value and cell text.

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

FTSCH

New Member
Joined
Aug 10, 2017
Messages
8
Is your data in one contiguous table? If so, have you tried using Data-Sort-Filter?
 

mychi11

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
May 11, 2020
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 10, 2017
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

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
Top