How to Speed Up VBA Code That Hides Rows in Excel
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.

- 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 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.
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.
Press `Alt + F11` to open the Visual Basic for Applications (VBA) Editor, and double-click the module containing your row-hiding macro.
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.
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)`.
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`.
At the end of your macro, insert `Application.ScreenUpdating = True` to re-enable screen refreshes so you can see the final result.

Filter Data Using an ADODB Connection
For exceptionally large datasets where looping is inherently slow, querying the workbook using an ADODB connection can extract the necessary data without manipulating rows.
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. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
- 2. Open Your Workbook: Launch WPS Spreadsheets and open your existing Microsoft Excel workbook (.xlsm).
- 3. Run Your Macros: Navigate to the Developer tab, enable macros, and execute your VBA scripts with improved responsiveness.

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.




