VBA Hide unhide depend on Cell Value where rows are same for different cell value.

[Screamer_87]

Hello colleauges,

I came across a problem to be honest last time I used VBA was 3 years ago, so today I'm preparing a live sheet which shift cells dependant on cell value (product number). I tried to tweak around the code but it seems it just getting commands just from last lines of code for value 9. I don't remember do I need to loop it or something if anyone can shed a light for me I would be thankful.
Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveSheet.Range("D7") = "1" Then
Rows("55:69").EntireRow.Hidden = True
Else
Rows("55:69").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "1" Then
Rows("85:112").EntireRow.Hidden = True
Else
Rows("85:112").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "1" Then
Rows("115:123").EntireRow.Hidden = True
Else
Rows("115:123").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "1" Then
Rows("133:141").EntireRow.Hidden = True
Else
Rows("133:141").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "2" Then
Rows("40:54").EntireRow.Hidden = True
Else
Rows("40:54").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "2" Then
Rows("85:112").EntireRow.Hidden = True
Else
Rows("85:112").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "2" Then
Rows("124:141").EntireRow.Hidden = True
Else
Rows("124:141").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "4" Then
Rows("55:69").EntireRow.Hidden = True
Else
Rows("55:69").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "4" Then
Rows("113:141").EntireRow.Hidden = True
Else
Rows("113:141").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "9" Then
Rows("55:69").EntireRow.Hidden = True
Else
Rows("55:69").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "9" Then
Rows("85:112").EntireRow.Hidden = True
Else
Rows("85:112").EntireRow.Hidden = False
End If

If ActiveSheet.Range("D7") = "9" Then
Rows("115:132").EntireRow.Hidden = True
Else
Rows("115:132").EntireRow.Hidden = False
End If

End Sub

 

[Yongle]

What I do is to HIDE every row in each range first and then unhide ONLY the ones I want visible

This may not be what you want, but it provides a method that you can adapt to your needs

Sub HideUnhide()
    Dim rng As Range, rng1 As Range, rng2 As Range, rng4 As Range, rng9 As Range
    Set rng1 = Union(Rows("55:69"), Rows("85:112"), Rows("115:123"), Rows("133:141"))
    Set rng2 = Union(Rows("40:54"), Rows("85:112"), Rows("124:141"))
    Set rng4 = Union(Rows("55:69"), Rows("113:141"))
    Set rng9 = Union(Rows("55:69"), Rows("85:112"), Rows("115:132"))
    
    Union(rng1, rng2, rng4, rng9).EntireRow.Hidden = True
    
    Select Case Range("D7")
        Case 1:     Set rng = rng1
        Case 2:     Set rng = rng2
        Case 4:     Set rng = rng4
        Case 9:     Set rng = rng9
    End Select
    
    rng.EntireRow.Hidden = False
End Sub

 

[dmt32]

Hi,
welcome to forum

one way maybe

Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr As Variant, m As Variant
    If Target.Address = "$D$7" Then
    
'build array of rows
    arr = Array("55:69,85:112,115:123,133:141", _
                 "40:54,85:112,124:141", _
                 "55:69,113:141", _
                 "55:69,85:112,115:132")
'unhide all rows in array
     Me.Range(Replace(Join(arr), " ", ",")).EntireRow.Hidden = False
'match target value
     m = Application.Match(Target.Value, Array(1, 2, 4, 9), 0)
'hide rows from selected array element
     If Not IsError(m) Then Me.Range(arr(m)).EntireRow.Hidden = True
    
    End If
End Sub

Notes:
1 - Option Base 1 statement - this MUST be placed at very TOP of your sheets code page OUTSIDE any procedure
2 - Solution assumes that value in D7 is being entered directly or changed by code. It will not work if changes are made via Formula - in this case solution would need updating.


Dave

 

[Yongle]

@Screamer_87
To test the method provided by @dmt32 paste the code into the SHEET module (not Module1, Module2 etc)

 

[Screamer_87]

Thank you #Yongle and #Dave thats perfectly working I took first one and amend to the present set up but works just spot on.
I forgot about that is better to hiding everything and then unhiding with VBA that's why I've tried other way an it doesn't work.
But finally something learned and it works.

Thanks once again.

 

[Yongle]

thanks for your feedback