VBA & Macro Problems

How to Use Excel VBA Range Variables to Hide Entire Rows

Rana Garcia Oct 23, 2026 871 views

Question details

The user needs to use an Excel VBA Range variable to select and hide entire worksheet rows.

How to Hide Entire Rows Using an Excel VBA Range Variable

Product: Excel
Device & OS: not provided
Scenario: Writing an Excel VBA macro to dynamically hide complete rows based on a defined cell range variable.
Observed behavior: Proper object assignment using the 'Set' keyword is required to define the range, followed by applying the EntireRow.Hidden property to successfully hide the targeted rows.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to successfully execute VBA code.

Solution 1Recommended

Hide Rows in the Active Worksheet

Use this approach when the VBA code runs directly inside the target worksheet's module.

When working within the same worksheet's module, you do not need to explicitly declare the worksheet object. You can directly assign the range using the 'Set' statement.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Select the Code Module

In the Project Explorer panel, double-click the specific worksheet (e.g., Sheet1) where you want to execute the code.

Add the VBA Code

Enter the following variable declaration and execution code: Dim RNG As Range | Set RNG = Range("A28:A31") | RNG.EntireRow.Hidden = True.

Run the Macro

Trigger the code through an event like Worksheet_Change or by pressing F5 inside the editor to execute the macro.

Object Assignment Required: Always remember to use the 'Set' keyword when assigning an object to a Range variable in VBA to prevent 'Object required' runtime errors.

Solution 2

Hide Rows in a Different Worksheet

Use this method if the macro is triggered from a standard module or targets a worksheet that is not currently active.

Advanced Spreadsheet Management

Run Excel VBA Macros Seamlessly in WPS Spreadsheet

WPS Office fully supports Excel VBA (Visual Basic for Applications) and Macros. You can easily write, edit, and execute your VBA scripts, including Range variables and EntireRow properties, to automate tasks exactly as you would in Microsoft Excel.

1. Open Your Macro Workbook in WPS: Launch WPS Spreadsheet and open your existing .xlsm or .xlsb file.
2. Enable Developer Tools: Navigate to the 'Developer' tab on the top menu ribbon to access macro functions.
3. Access the VBA Editor: Click on 'Visual Basic' or press Alt + F11 on your keyboard to open the code editor.
4. Run Your Range Code: Input your Range assignment code and click 'Run' to seamlessly hide rows using your macro.

Fully compatible with Microsoft Excel VBA syntax and macro formats (.xlsm, .xlsb).Includes a built-in, familiar VBA editor to easily debug and modify scripts.Lightweight software architecture ensuring fast loading times for large datasets.Cost-effective and powerful alternative for advanced data analysis and automation.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting an 'Object required' error when defining my Range?

This error usually occurs if you forget to use the 'Set' keyword for object assignment. In VBA, because a Range is an object and not a simple data type, you must use `Set RNG = Range("A1")` instead of just `RNG = Range("A1")`.

How do I unhide the rows using the exact same Range variable?

To unhide the rows, simply change the boolean value of the Hidden property to False using the command: `RNG.EntireRow.Hidden = False`.

Can I hide non-contiguous rows using a single Range variable?

Yes, you can define a non-contiguous range by separating the individual ranges with a comma. For example: `Set RNG = Range("A1:A5, A10:A15")`, and then apply `RNG.EntireRow.Hidden = True` to hide both segments at once.

Excel & Data Problems