How to Automatically Hide Rows based on Formula driven Cell Value

Want to learn how to design a salary structure? Check: www.caripros.com/design-salar...
FREE template for my video: Excel for HR - Create Annual Employee Salary Increase Template from Scratch. You can download and try it out yourself here: bit.ly/2MLLdb7
FREE actual workbook for my video "Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner". You can download and try it out yourself here: bit.ly/2UmeX2v
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:
caripros-hr-analytics.teachab... Topic: Excel Macro - How to Automatically Hide Rows based on Cell Value
Scenario: You want to automatically hide rows/content when you select certain value in a specified cell
Function: Macro
Related Video:
How to Automatically Hide Rows based on Cell Value • How to Automatically H...
How to Automatically Hide Columns based on Cell Value: • How to Automatically H...
Automatically Hide Nonsequential Rows based on Cell Value: • How to Automatically H...
**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*****
If you have specific question regarding your issue, you can email me at the email here goo.gl/WejijZ Note that there will be a fee of US$200 charged for solving your issue. The turnaround is within 24 hours. Any follow-up issue in 3 days will also be answered with no charge. Payment link: www.paypal.me/caripros
*****More Videos in Playlists*****
Power BI for Beginners: bit.ly/3ivKitD
Power BI for Advanced Users: bit.ly/3lE9zmO
Excel for HR goo.gl/JdeVnd
Excel for HR - Master Class goo.gl/LYfq2f
Excel Macro - Beginner goo.gl/Yae5nc
Excel Macro/VBA - Splitting a Master File goo.gl/m8CHya
Excel Macro/VBA - Auto-hide Rows or Columns bit.ly/2Mzteb5
Excel Charts Data Visualization goo.gl/2ao6BP
Excel Vlookup Function goo.gl/kP2Wpz
Excel Pivot Table Function goo.gl/rukkPs
Excel Array Function goo.gl/i4sQH8
Excel Index and Match Function goo.gl/i7VGU4
Excel Solver/Goal Seek Functions goo.gl/FTkTnj
Excel Cell Formatting Solutions goo.gl/gpa6MY
HR Analytics - Merit Matrix goo.gl/Koy7co
HR Analytics - Salary Structure goo.gl/uZBnFa
Excel Tricks goo.gl/TeqGDw
Excel Troubleshooting goo.gl/bdY5by
Fun HR Topics goo.gl/7zVg8h
For more successful stories, view at: caripros.com/index.php/success...
#ExcelforHR#HRAnalytics#Excel#HR

[@pinnasuresh]

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

[@cstomi990]

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

[@salsabilavahishta4480]

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

[@CariprosHRAnalytics]

Thank you Salsabila!

[@MohAboAbdo]

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

[@michaelcrossley5661]

Thanks Rachel

[@pravinbhaichavada5097]

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

[@MIMGURU]

Good👍

[@jennmostajo820]

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! :)

[@AnonymousHippo5]

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!

[@akio6505]

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.

[@kurtcabahug9496]

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?

[@grahamlejeune215]

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

[@akhil.ssunish4289]

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? 😀

[@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??

[@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": 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
Application.EnableEvents = True
End Sub

[@heatherm3776]

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

[@dediksudjarwadi7350]

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

[@CariprosHRAnalytics]

OK this seems a common question. I will make another video to explain this issue. stay tuned

[@eX-xr9ek]

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

[@CariprosHRAnalytics]

It shall not matter. You just specify which rows you want to hide/unhide in your VBA code.

[@QQ-nb2ic]

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