How to Automatically Hide Rows based on Formula driven Cell Value

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 Video:

***Macro Code for Auto-Hide Rows based on Formula driven Cell Value***
Private Sub Worksheet_Calculate()
Dim Country As Range
Set Country = Range("C5")

Select Case Country
Case Is = "Canada": Rows("19:25").EntireRow.Hidden = True
Rows("7:18").EntireRow.Hidden = False
Case Is = "India": Rows("19:25").EntireRow.Hidden = False
Rows("7:18").EntireRow.Hidden = True
End Select
End Sub

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

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

#ExcelforHR#HRAnalytics#Excel#HR

[pinnasuresh]

Hi Caripros your videos are very helpful and simply explain how excel vba code works. Great job.

[erdethernavntaget]

To anyone not being able to get this to work, like myself at first, try copying "Application.EnableEvents = False" before the code and "Application.EnableEvents = True" after the code. This worked for me. You may need to restart excel before use.

So the code becomes:

Application.EnableEvents = False
Private Sub Worksheet_Calculate()
Dim Country As Range
Set Country = Range("C5")

Select Case Country
Case Is = "Canada": = True
Rows("7:18").EntireRow.Hidden = False
Case Is = "India": = False
Rows("7:18").EntireRow.Hidden = True
End Select
Application.EnableEvents = True
End Sub

[cstomi]

Amazing, this was exactly the thing and code what I needed! Thank you very much, you are the best!
Regards from Hungary!

[salsabilavahishta]

Hi Caripros, just watched a few of your videos. Keep posting! I love that you listen to our concern, thanks for being very helpful:)

[MohAboAbdo]

Thank you very much for this great video and for this valuable informations.

[pravinbhaichavada]

thanks a lot ma'am, great work. once again hearty thanks....

[AnonymousHippo]

Do have a Macro to auto hide row below if a cell above is empty? So if the one cell above is filled it will unhide a single row below it? Thanks!

[akio]

Great! I want to set it for 5 users and not country but obviously i dont want them to see other data when they open the sheet. Idea is when a user opens it then only his data will be visible to him. How do u achieve that? Thanks in advance.

[jennmostajo]

Thanks for this! Can you tell me why when I added a reset button that will reset the value of the dropdown list to "Please select" I am getting an error? It has something to do with hide/unhide codes in the worksheet. Please help. Thanks! :)

[kurtcabahug]

Thanks for the great tutorial and Im using this trick for month but why is that in some other cases, "runtime error 1004, Method range of object Worksheet failed" appears, how to solve this problem?

[akhil.ssunish]

Your videos are really helpful for a beginer like me. I tried this code but unfortunately the excel is crashing whenever I use this code. Do you have any solution for this? 😀

[grahamlejeune]

How to you loop inside of this code to get it to hide certain rows based on the cells value going down a column?

[noosajunctionchiropractic]

Hi. I am struggling with your code because I'm not super confident with VBA. One of your videos shows how to hide a series of rows only if you manually select an option from a dropdown. Then another video shows the same outcome if the value in that one cell is derived by a formula BUT in that video that formula is driven by another cell where you must ALSO manually select an option. Is there a way to not have to make a manual change? Where Excel can look at the formula result each time you display the worksheet, and if the value = (for example) 0, then hide all relevant rows? ALSO is it possible to have more than ONE reference cell in the code so that each row hides based on a reference in that row - for example: Row 1 hides when cell A1 =0, Row 2 hides when cell A2 = 0 etc. In another video you showed a method where you can hide a series of rows by defining "i as Integer" - creating a loop that hides all rows in a certain range, but once again, this references one specific cell only to run or launch the macro and requires a manual change by the user. I would like this to be automatic like I mentioned above...where first, each row hides based on its own reference and second, where excel auto-runs or checks the formula value/result, each time the worksheet opens. Can you help?? Is this possible??

[dediksudjarwadi]

its works but can you explain how to make excel stop from calculating after selecting the data?

[heatherm]

Your tutorials are great, however, I cannot get my toggle button to work when trying to hide rows with zero value

[eX-xrek]

how if the rows are dynamic, i mean sometimes have 10 rows or 5 rows..

[QQ-nbic]

Need to use zoom so we can see what you are doing..