Скачать или смотреть Hide and Unhide Rows in Excel Based on Cell Value Using VBA

Learn how to effectively `hide` and `unhide` a range of rows in Excel by utilizing VBA, allowing for a more dynamic worksheet experience.
---
This video is based on the question https://stackoverflow.com/q/73301408/ asked by the user 'Methodikal' ( https://stackoverflow.com/u/19732341/ ) and on the answer https://stackoverflow.com/a/73303056/ provided by the user 'VBasic2008' ( https://stackoverflow.com/u/9814069/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Hide and Unhide a range of rows based on the value in a cell

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Hide and Unhide Rows in Excel Based on Cell Value Using VBA

If you're working with Excel and need to dynamically show or hide rows based on the value in a specific cell, like those altered by radio buttons, you might hit a few roadblocks. This is a common task in Excel, but it can be tricky, especially when it involves protecting sheets and maintaining functionality. In this guide, we'll walk through the process of achieving this goal using Visual Basic for Applications (VBA), ensuring that your code runs smoothly even when your worksheet is protected.

The Problem

You have a situation where you want to hide or unhide rows (specifically rows 63 to 93) on a worksheet named "Feedback" based on the value of cell L37, which toggles between 1 and 2 using radio buttons. However, you run into issues particularly when the worksheet is protected, and your previous macro no longer works seamlessly. Let's break down the problems you might face:

Active Workbook Dependency: If the wrong workbook is active, the macro will fail.

Missed Worksheet Qualification: Not properly qualifying the worksheet can lead to errors.

Improper If-Else Logic: Using Else instead of ElseIf means that your conditions may not be correctly evaluated.

Error Handling: If the cell has an error value, it can cause the code to fail unexpectedly.

The Solution

Step 1: Revise Your Code

Start by utilizing the ThisWorkbook reference to ensure that your code targets the correct worksheets, regardless of what might be currently active. Below is the revised code to manage row visibility responsibly:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Automate the Process

To make this process seamless, you can automate row hiding and showing without the need for button clicks. You can achieve this by using the Worksheet_Change event in the Worksheet module for "Feedback". Here's how:

Open the VBE (Visual Basic for Applications) Editor.

Find your "Feedback" sheet in the Project Explorer (often labeled like Sheet1 (FeedBack)).

Double-click on the sheet and add the code below:

[[See Video to Reveal this Text or Code Snippet]]

Key Points to Remember

Always qualify your worksheets: Use ThisWorkbook.Worksheets("Feedback") to avoid errors with the active workbook.

Error Handling: Using CStr prevents errors from crashing your code when the cell value is not numeric or an unexpected value.

Automated Row Handling: The Worksheet_Change event eliminates the need for user actions (such as clicking buttons), making the experience smoother and more intuitive.

Conclusion

By implementing the above VBA code, you can effectively hide and unhide rows in Excel based on the value of a cell. This solution not only circumvents previously encountered issues but also enhances the usability of your Excel workbook significantly. Give it a try, and you'll be able to manage your data presentation effortlessly!