Expand or Collapse Excel Rows Based on Cell Selection Using VBA
Question details
The user wants to automatically show, hide, or resize specific rows in a spreadsheet whenever a particular cell is clicked or selected.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating spreadsheet interactivity so that selecting a specific cell or range triggers related data rows to expand or collapse dynamically.
- Observed behavior
- Rows will seamlessly hide, unhide, or adjust their height based on the user's active cell selection without needing manual filtering.
Ensure that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your spreadsheet security settings so the VBA code can execute properly.
Use the Worksheet_SelectionChange Event to Toggle Row Visibility
Implement VBA's built-in worksheet event to trigger row visibility or height changes instantly upon cell selection.
The Worksheet_SelectionChange event automatically runs VBA code whenever a new cell or range is selected on a specific worksheet. By setting conditions within this event, you can instruct Excel to hide or reveal target rows when a specific cell is activated.
Right-click the worksheet tab at the bottom of your Excel window and select View Code from the context menu.
In the code window, select Worksheet from the left dropdown menu and SelectionChange from the right dropdown menu to generate the event procedure.
Inside the procedure, add your logic to check if the Target cell intersects with your desired trigger cell. Use the Target.EntireRow.Hidden property to set the rows to True (hidden) or False (visible).
If you are expanding rows by modifying height rather than unhiding them completely, set a specific height using Target.RowHeight = [value] and disable automatic row height.
Go to File, click Save As, and choose Excel Macro-Enabled Workbook (*.xlsm) from the format dropdown to ensure your automation is preserved.
How to Automate Row Expansion in WPS Spreadsheet
WPS Office provides robust built-in VBA support, allowing you to create interactive spreadsheets, automate row visibility, and manage complex macros seamlessly without purchasing expensive software.
- 1. Open the Developer Tab: Open your workbook in WPS Spreadsheet and navigate to the Developer tab on the top ribbon.
- 2. Launch the VBA Editor: Click the VBA Editor button to open the macro coding interface.
- 3. Select the Target Worksheet: In the left Project Explorer panel, double-click the specific worksheet where you want the expand/collapse action to occur.
- 4. Add the Selection Code: Paste your Worksheet_SelectionChange script into the module to define the logic for hiding or showing rows.
- 5. Save Your Document: Click Save As and select the .xlsm file type to keep your macros functional for future use.
Frequently Asked Questions
Why is the VBA code not hiding the rows when I click a cell?
This typically occurs if macros are disabled in your Trust Center settings or if application events have been turned off. Ensure macros are enabled, and run 'Application.EnableEvents = True' in the VBA Immediate Window.
Can I expand or collapse columns instead of rows using this method?
Yes, the logic is identical. Instead of using EntireRow, you can modify the VBA code to target columns by using the Target.EntireColumn.Hidden property based on your cell selection.
Will this macro work if I share the file with users who don't have VBA enabled?
No, this interactive expand/collapse feature relies entirely on VBA processing. Users must open the file in a desktop spreadsheet program that supports VBA and allow macros to run for the interactivity to function.
How do I prevent row heights from auto-adjusting when unhidden?
You can manually set a fixed row height in your VBA script using Target.RowHeight = 15 (or your preferred value). This forces the row to adhere to your layout rather than auto-fitting the contents.
