VBA & Macro Problems

How to Speed Up VBA Code That Hides Rows in Excel

Emma BrownEmma Brown Nov 10, 2026 869 views

Question details

The user needs to optimize an Excel VBA macro that is currently running too slowly when hiding multiple rows based on specific cell conditions.

How to Speed Up VBA Code for Hiding Rows in Excel
Product
Excel
Device & OS
not provided
Scenario
Running a VBA macro to evaluate a large dataset and hide specific rows that do not match a target criteria (e.g., a specific date).
Observed behavior
The macro executes extremely slowly because hiding rows one by one inside a loop triggers continuous worksheet updates and screen refreshes.
Before you start

Before modifying your macro, make sure to save a backup copy of your macro-enabled workbook (.xlsm). This ensures your original data and code remain intact if the updated VBA script encounters an unexpected error during testing.

Solution 1Recommended

Use the Union Method and Disable Screen Updating

The most effective way to speed up row-hiding macros is to freeze screen refreshes and batch all row-hide operations into a single command using the Union function.

Hiding rows individually forces Excel to recalculate and redraw the interface for every single row, which drastically reduces execution speed. By using the VBA `Union` function, you can collect all matching cells into a single range object. Once the loop finishes, you can hide the entire collected range simultaneously, cutting processing time from minutes to seconds.

1
Open the VBA Editor

Press `Alt + F11` to open the Visual Basic for Applications (VBA) Editor, and double-click the module containing your row-hiding macro.

2
Disable Screen Updating

At the very beginning of your macro code, insert the line `Application.ScreenUpdating = False` to prevent Excel from refreshing the screen while the code runs.

3
Collect Cells using Union

Declare a Range variable (e.g., `rngToggle`). Inside your loop, check your condition. If met, add the cell to the range using `If rngToggle Is Nothing Then Set rngToggle = cell Else Set rngToggle = Union(rngToggle, cell)`.

4
Hide Rows in One Action

After the loop completes, hide all the collected rows at once by adding the line `If Not rngToggle Is Nothing Then rngToggle.EntireRow.Hidden = True`.

5
Restore Screen Updating

At the end of your macro, insert `Application.ScreenUpdating = True` to re-enable screen refreshes so you can see the final result.

Use the Union Method and Disable Screen Updating
Implement Error Handling: Always include error handling in your macro (e.g., `On Error GoTo ErrorHandler`) to ensure `Application.ScreenUpdating = True` runs even if the code fails, preventing the Excel interface from freezing.
Free Microsoft Office alternative

Experience Faster Spreadsheet Processing with WPS Office

Struggling with slow macros and performance bottlenecks in Microsoft Excel? WPS Office provides a free, lightweight, and highly compatible alternative. Enjoy swift data processing, a familiar user interface, and seamless migration for your heavy macro-enabled workbooks.

  1. 1. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
  2. 2. Open Your Workbook: Launch WPS Spreadsheets and open your existing Microsoft Excel workbook (.xlsm).
  3. 3. Run Your Macros: Navigate to the Developer tab, enable macros, and execute your VBA scripts with improved responsiveness.
Lightweight architecture for faster file loading and smoother data processingHighly compatible with Microsoft Excel macro formats including .xlsm and .xlsbFamiliar user interface that requires zero learning curve for Excel usersFree to download and use with excellent system resource management
microsoft office alternative - wps office

Frequently Asked Questions

Why does hiding rows inside a VBA loop take so long?

When VBA hides a row individually, Excel automatically recalculates formulas, triggers worksheet change events, and redraws the screen layout. If this happens inside a loop running thousands of times, the constant refreshing drastically slows down the execution speed.

What is the Union method in Excel VBA?

The Union method is a built-in VBA function used to combine multiple ranges into a single Range object. In the context of hiding rows, it allows you to group all target cells dynamically during a loop and apply the `.EntireRow.Hidden` command to the combined range all at once, which is significantly faster.

Does Application.ScreenUpdating affect other open programs?

No, the `Application.ScreenUpdating` property only applies to the specific Excel application instance that is currently running the macro. It temporarily pauses visual updates within Excel to allocate more processing power to your code. It should always be turned back to `True` before the macro finishes.