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

Screamer_87

New Member
Joined
Oct 17, 2019
Messages
2
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

Code:
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
 
Upvote 0
Hi,
welcome to forum

one way maybe

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top