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

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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Ryle23

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

1618310506589.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
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).
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
 

Ryle23

New Member
Joined
Apr 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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
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.
 

Ryle23

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

ADVERTISEMENT

1618316991057.png
 

Ryle23

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

on case = "50" it should both be false
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Ryle23

New Member
Joined
Apr 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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..
 

Ryle23

New Member
Joined
Apr 13, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
i guess ill just have to play with the protection to where i should place it.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

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