Hide/Unhide Rows on different sheets based on cell value from one main sheet

R

Ryle23

New Member
Joined
Apr 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Good day,

I really dont have any idea about codes and im just searching for ideas on Youtube.

My case here is that I want to hide rows on "Masterlist" sheet [Main sheet where cell value will be updated] and also on other sheets (Attendance , Raw Scores, Final Ratings).

this is my current code (This works when I want to update the Masterlist sheet)

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect Password:="1234"

If Not Application.Intersect(Range("I2"), Range(Target.Address)) Is Nothing Then
    
    Select Case Target.Value
        
        Case Is = "50": Rows("12:61").EntireRow.Hidden = False
                        
        Case Is = "49": Rows("12:60").EntireRow.Hidden = False
                        Rows("61").EntireRow.Hidden = True
                                              
        Case Is = "48": Rows("12:59").EntireRow.Hidden = False
                        Rows("60:61").EntireRow.Hidden = True
                        
        Case Is = "47": Rows("12:58").EntireRow.Hidden = False
                        Rows("59:61").EntireRow.Hidden = True
                        
        Case Is = "46": Rows("12:57").EntireRow.Hidden = False
                        Rows("58:61").EntireRow.Hidden = True
        
        Case Is = "45": Rows("12:56").EntireRow.Hidden = False
                        Rows("57:61").EntireRow.Hidden = True
                        
        Case Is = "44": Rows("12:55").EntireRow.Hidden = False
                        Rows("56:61").EntireRow.Hidden = True
                                    
        Case Is = "43": Rows("12:54").EntireRow.Hidden = False
                        Rows("55:61").EntireRow.Hidden = True
                        
        Case Is = "42": Rows("12:53").EntireRow.Hidden = False
                        Rows("54:61").EntireRow.Hidden = True
                        
        Case Is = "41": Rows("12:52").EntireRow.Hidden = False
                        Rows("53:61").EntireRow.Hidden = True
                        
        Case Is = "40": Rows("12:51").EntireRow.Hidden = False
                        Rows("52:61").EntireRow.Hidden = True
                                        
        Case Is = "39": Rows("12:50").EntireRow.Hidden = False
                        Rows("51:61").EntireRow.Hidden = True
                       
        Case Is = "38": Rows("12:49").EntireRow.Hidden = False
                        Rows("50:61").EntireRow.Hidden = True
                        
        Case Is = "37": Rows("12:48").EntireRow.Hidden = False
                        Rows("49:61").EntireRow.Hidden = True
                        
        Case Is = "36": Rows("12:47").EntireRow.Hidden = False
                        Rows("48:61").EntireRow.Hidden = True
        
        Case Is = "35": Rows("12:46").EntireRow.Hidden = False
                        Rows("47:61").EntireRow.Hidden = True
                        
        Case Is = "34": Rows("12:45").EntireRow.Hidden = False
                        Rows("46:61").EntireRow.Hidden = True
                                    
        Case Is = "33": Rows("12:44").EntireRow.Hidden = False
                        Rows("45:61").EntireRow.Hidden = True
                        
        Case Is = "32": Rows("12:43").EntireRow.Hidden = False
                        Rows("44:61").EntireRow.Hidden = True
                        
        Case Is = "31": Rows("12:42").EntireRow.Hidden = False
                        Rows("43:61").EntireRow.Hidden = True
                        
        Case Is = "30": Rows("12:41").EntireRow.Hidden = False
                        Rows("42:61").EntireRow.Hidden = True
                        
        Case Is = "29": Rows("12:40").EntireRow.Hidden = False
                        Rows("41:61").EntireRow.Hidden = True
                       
        Case Is = "28": Rows("12:39").EntireRow.Hidden = False
                        Rows("40:61").EntireRow.Hidden = True
                        
        Case Is = "27": Rows("12:38").EntireRow.Hidden = False
                        Rows("39:61").EntireRow.Hidden = True
                        
        Case Is = "26": Rows("12:37").EntireRow.Hidden = False
                        Rows("38:61").EntireRow.Hidden = True
        
        Case Is = "25": Rows("12:36").EntireRow.Hidden = False
                        Rows("37:61").EntireRow.Hidden = True
                        
        Case Is = "24": Rows("12:35").EntireRow.Hidden = False
                        Rows("36:61").EntireRow.Hidden = True
                                    
        Case Is = "23": Rows("12:34").EntireRow.Hidden = False
                        Rows("35:61").EntireRow.Hidden = True
                        
        Case Is = "22": Rows("12:33").EntireRow.Hidden = False
                        Rows("34:61").EntireRow.Hidden = True
                        
        Case Is = "21": Rows("12:32").EntireRow.Hidden = False
                        Rows("33:61").EntireRow.Hidden = True
                        
        Case Is = "20": Rows("12:31").EntireRow.Hidden = False
                        Rows("32:61").EntireRow.Hidden = True
                        
        Case Is = "19": Rows("12:30").EntireRow.Hidden = False
                        Rows("31:61").EntireRow.Hidden = True
                       
        Case Is = "18": Rows("12:29").EntireRow.Hidden = False
                        Rows("30:61").EntireRow.Hidden = True
                        
        Case Is = "17": Rows("12:28").EntireRow.Hidden = False
                        Rows("29:61").EntireRow.Hidden = True
                        
        Case Is = "16": Rows("12:27").EntireRow.Hidden = False
                        Rows("28:61").EntireRow.Hidden = True
        
        Case Is = "15": Rows("12:26").EntireRow.Hidden = False
                        Rows("27:61").EntireRow.Hidden = True
                        
        Case Is = "14": Rows("12:25").EntireRow.Hidden = False
                        Rows("26:61").EntireRow.Hidden = True
                                    
        Case Is = "13": Rows("12:24").EntireRow.Hidden = False
                        Rows("25:61").EntireRow.Hidden = True
                        
        Case Is = "12": Rows("12:23").EntireRow.Hidden = False
                        Rows("24:61").EntireRow.Hidden = True
                        
        Case Is = "11": Rows("12:22").EntireRow.Hidden = False
                        Rows("23:61").EntireRow.Hidden = True
                        
        Case Is = "10": Rows("12:21").EntireRow.Hidden = False
                        Rows("22:61").EntireRow.Hidden = True
                        
        Case Is = "9": Rows("12:20").EntireRow.Hidden = False
                       Rows("21:61").EntireRow.Hidden = True
                       
        Case Is = "8": Rows("12:19").EntireRow.Hidden = False
                       Rows("20:61").EntireRow.Hidden = True
                        
        Case Is = "7": Rows("12:18").EntireRow.Hidden = False
                       Rows("19:61").EntireRow.Hidden = True
                        
        Case Is = "6": Rows("12:17").EntireRow.Hidden = False
                       Rows("18:61").EntireRow.Hidden = True
        
        Case Is = "5": Rows("12:16").EntireRow.Hidden = False
                       Rows("17:61").EntireRow.Hidden = True
                        
        Case Is = "4": Rows("12:15").EntireRow.Hidden = False
                       Rows("16:61").EntireRow.Hidden = True
                                    
        Case Is = "3": Rows("12:14").EntireRow.Hidden = False
                       Rows("15:61").EntireRow.Hidden = True
                        
        Case Is = "2": Rows("12:13").EntireRow.Hidden = False
                       Rows("14:61").EntireRow.Hidden = True
                        
        Case Is = "1": Rows("12").EntireRow.Hidden = False
                       Rows("13:61").EntireRow.Hidden = True
                       
    End Select
        
End If

ActiveSheet.Protect Password:="1234"

End Sub

on Masterlist sheet, cell I2 is where I place the value to which i decide on how many students I want to be listed on my table.

this corresponds to rows 8 to 57 on Attendance sheet

and rows 21 to 70 on Raw Scores and Final Ratings sheet.
 

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)
Sort by date Sort by votes
1618310490243.png

1618310506589.png
 
Upvote 0
Ryle23 said:
My case here is that I want to hide rows on "Masterlist" sheet [Main sheet where cell value will be updated] and also on other sheets (Attendance , Raw Scores, Final Ratings).
Click to expand...
You can hide rows on other sheets by adding the sheet name to the range reference.

So in your current code, when you do not use a sheet reference, like this:
VBA Code: 
Rows("19:61").EntireRow.Hidden = True
it just runs against whatever sheet is the current active sheet.

However, if you want to run it against some other sheet, just add the sheet name reference, i.e.
VBA Code: 
Sheets("Masterlist").Rows("19:61").EntireRow.Hidden = True
 
Upvote 0
Joe4 said:
You can hide rows on other sheets by adding the sheet name to the range reference.

So in your current code, when you do not use a sheet reference, like this:
VBA Code: 
Rows("19:61").EntireRow.Hidden = True
it just runs against whatever sheet is the current active sheet.

However, if you want to run it against some other sheet, just add the sheet name reference, i.e.
VBA Code: 
Sheets("Masterlist").Rows("19:61").EntireRow.Hidden = True
Click to expand...
i tried doing this..i get an error 1004 saying unable to set the hidden property of the range class..

btw my sheets contains formulas and locked/hidden contents.
 
Upvote 0
It sounds like you may have protection set to prevent that from happening.
What happens if you try hide that particular range manually?
If you cannot do it manually because of protection, then you won't be able to do it by code either without first unprotecting the range.

Typically, in the VBA code, you would unprotect the range, do the hiding/unhiding, then re-protect the range.
You can get a lot of that code using the Macro Recorder.
 
Upvote 0
ok..i kinda resolved it by removing the protections on codes and even on manual protection..

now is there a way to protect it again using code..
 
Upvote 0
i guess ill just have to play with the protection to where i should place it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
799
Peter_SSs
Peter_SSs
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
A
  • Solved
how to protect sheets vba code
2
Replies
19
Views
1K
Scuba94
S
M
  • Question Question
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K
lonlyboy
  • Question Question
Hides Rows based on cell value on multiple worksheets
Replies
3
Views
385
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

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