How to Automatically Hide Rows based on Cell Value - Sheet Protected and Formula Hidden

New course Launched! I created it to show you step-by-step how to design a salary structure with regression analysis in Excel. Check out the detail here:
Scenario: You want to automatically hide rows/content when you select certain value in a specified cell
Function: Macro

Related Videos:

***Macro Code***
Private Sub Worksheet_Calculate()
Dim Country, Location As Range
Set Country = Range("C5")
Set Location = Range("C7")

'sheet is now protected
ActiveSheet.Unprotect Password = "12345"
Select Case Country
Case Is = "Canada": Rows("21:35").EntireRow.Hidden = True 'both India ranges are hidden
Rows("9:20").EntireRow.Hidden = False 'Canada range is shown
Case Is = "India":
Rows("9:20").EntireRow.Hidden = True 'Canada range is hidden
Rows("21:35").EntireRow.Hidden = False 'both India ranges are shown
End Select

Select Case Location
Case Is = "Mumbai"
Rows("9:20").EntireRow.Hidden = True 'Canada range is hidden
Rows("21:27").EntireRow.Hidden = False 'Mumbai range is shown
Rows("29:35").EntireRow.Hidden = True 'non-Mumbai location range is hidden
Case Is = "Non-Mumbai Location"
Rows("9:20").EntireRow.Hidden = True ' Canada range is hidden
Rows("21:27").EntireRow.Hidden = True 'Mumbai range is hidden
Rows("29:35").EntireRow.Hidden = False 'non-Mumbai location range is shown
End Select
ActiveSheet.Protect Password = "12345"
End Sub

******Follow-up Consulting Services******

******More Videos in Playlists******

#ExcelforHR#HRAnalytics#Excel#HR

[jasonherdy]

DUDE I LOVE YOU SO MUCH THIS SOLUTION IS SO SPECIFIC AND YOU EXPLAINED IT CLEARLY!!
I will try it and update you if it works!

[power-spkm]

Your videos are sooo good and ooo underrated
it is so helpfull that you explain the problem before starting the solution
... thank you so much

[gurmanjitkaur]

You are great, Thank you very much for such a useful video

[MohAboAbdo]

Thank you very much for this great video and for this extensive explanation.

[lreid]

Is there any way to use a cell formula to unhide an entire row? I don't want to use VBA or right-click.

Thanks