VBA & Macro Problems

How to Fix Excel VBA Errors When Hiding Named Ranges

Camila Milosovich Sep 27, 2026 868 views

Question details

The user needs to resolve VBA runtime errors triggered when using a drop-down list to hide or unhide dynamically referenced named ranges in an Excel worksheet.

How to Fix Excel VBA Errors When Hiding Named Ranges

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using a Worksheet Change event and a drop-down menu to trigger a Select Case macro that hides or unhides specific named ranges.
Observed behavior: Excel returns 'Procedure too large' or 'Unable to set the Hidden property of the Range class' errors due to poorly optimized Select Case statements or invalid named-range references.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and verify in the Name Manager that all named ranges referenced in your VBA code correctly exist.

Solution 1Recommended

Optimize the Worksheet Change Event and Validate Ranges

Use this method to streamline your VBA code by hiding all relevant rows or columns first, then selectively unhiding only the ranges associated with the drop-down selection.

Large 'Select Case' procedures can trigger a 'Procedure too large' error in VBA. By resetting the visibility of all target columns or rows before executing the Select Case statement, you significantly reduce the amount of redundant code.

Additionally, ensuring the 'Hidden' property is applied to entire rows or columns prevents runtime errors related to partial range manipulation.

Target the Specific Cell

Use 'If Not Intersect(Target, Range("A1")) Is Nothing Then' to ensure the macro only runs when the specific drop-down cell (e.g., A1) is changed.

Hide All Target Ranges Initially

Apply the Hidden property to the entire block of columns or rows (for example, Columns("B:N").Hidden = True) before evaluating the drop-down value.

Unhide Specific Ranges

Use a 'Select Case Target.Value' block to evaluate the drop-down selection, then unhide the specific named range using 'Columns(Range("rng_003").EntireColumn.Address).Hidden = False'.

Handle Multiple Ranges

If a selection requires displaying multiple ranges simultaneously, create a separate Case (e.g., Case "3-4") and unhide each corresponding named range sequentially within that block.

Optimize the Worksheet Change Event and Validate Ranges

Apply to Entire Rows or Columns: The 'Hidden' property must be applied to an entire row or column. Attempting to hide a partial range (like A1:B10) will result in the 'Unable to set the Hidden property' error.

Advanced Macro Support

Execute and Debug VBA Macros Seamlessly with WPS Office

WPS Spreadsheet provides comprehensive support for VBA (Visual Basic for Applications). You can easily run, edit, and troubleshoot your macro codes, including complex Select Case statements and named range properties, directly within a familiar and highly compatible interface.

1. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the named ranges.
2. Access the Developer Tab: Navigate to the 'Developer' tab located on the top ribbon.
3. Open the VBA Editor: Click on 'Visual Basic' or 'Macros' to launch the built-in VBA editor.
4. Edit Your Code: Locate your Worksheet_Change event script in the Project Explorer and apply the optimized Select Case logic.
5. Save and Run: Save the document as an .xlsm file and test your drop-down list to see the named ranges hide and unhide flawlessly.

Fully compatible with Microsoft Excel macro (.xlsm) formatsBuilt-in VBA editor for advanced macro troubleshooting and debuggingLightweight software footprint with fast macro execution speedsFree and intuitive interface for seamless transition

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get the 'Unable to set the Hidden property of the Range class' error?

This error typically occurs if you try to apply the Hidden property to a block of individual cells instead of complete rows or columns. Ensure your VBA code uses '.EntireRow.Hidden = True' or '.EntireColumn.Hidden = True'.

How can I check if my named ranges are valid in VBA?

You can verify named ranges by navigating to Formulas > Name Manager in the ribbon. In VBA, attempting to reference a misspelled or deleted named range will throw a runtime error. It is best practice to validate your named ranges in the Name Manager before running your macro.

How do I trigger a macro only when a drop-down list is changed?

Use the 'Worksheet_Change' event and wrap your macro code in an 'Intersect' condition, such as 'If Not Intersect(Target, Range("A1")) Is Nothing Then'. This condition guarantees the code only executes when the specific drop-down cell (in this case, A1) is modified.

Excel & Data Problems