Hide/unhide rows based on cell value generated by

[david91]

Dear All,

I am trying to hide/unhide rows 14:17 in my workbook, but no matter what kind of coding I insert and modify in VBA, nothing works.

What I have so far:

Object X combo box (which I want to keep!) (drop down menu) between E:10 - J:10 which generates value/numbers 1-11 in cell F:10 based on what I choose in the drop down menu.
The 0 value or default value which is "Choose from dropdown menu" is 1 in cell F:10, which I do not wish to change.

What I wish to achieve (alt. 1):

If value in F:10 = 1-8, then entire row 14:17 is hidden (which should be default).
If value in F:10 = 9-11, then entire row 14:17 is unhidden.

What I wish to achieve (alt. 2 if alt. 1 is not applicable/possible):

If the above mention is not going to work bc of the combo box then:
Assume the following names in the combo box:
1 = "A"
2 = "B"
3 = "C"
4 = "D"
5 = "E"
6 = "F"
7 = "G"
8 = "H"
9 = "I"
10 = "J"
11 = "K"

If value in F:10 = "A"-"H" (names in the combo box), then entire row 14:17 is hidden (which should be default).
If value in F:10 = "I"-"K", then entire row 14:17 is unhidden.

I appreciate all the answers I can get, and if my question is unclear, please let me know!

Wish everyone a pleasant day!

 

[Osvaldo Palmeiro]

Hi, David.
Try this code pasted into the sheet Module.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, [E10:J10]) Is Nothing Then
  If [F10] >= 1 And [F10] <= 8 Then Rows("14:17").Hidden = True Else Rows("14:17").Hidden = False
 End If
End Sub

 

[david91]

Thank you for your answer Osvaldo.

Unfortunately, it did not change anything, it did not hide nor unhide when choosing.
Iforgot to mention that I want this applied for multiple sheets, sheet 2 3 and 4.

I tried the following code for sheet to, but the only thing it does is to hide the rows, and do not unhide at all:

Sub HURows()
Dim BeginRow As Long
Dim EndRow As Long
Dim ChkCol As Long


BeginRow = 14
EndRow = 17
ChkCol = 10


ActiveSheet.DisplayPageBreaks = False


With Application
.ScreenUpdating = True


End With
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 9 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
ElseIf Cell(RowCnt, ChkCol).Value = 10 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
ElseIf Cells(RowCnt, ChkCol).Value = 11 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt


End Sub