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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
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:

Screamer_87

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
thanks for your feedback (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,916
Messages
5,621,602
Members
415,847
Latest member
AlpinoHirsch

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