VBA & Macro Problems

How to Speed Up an Excel Macro That Clears Input Cells

Elise WilliamsElise Williams Nov 2, 2026 869 views

Question details

The user wants to improve the performance of a VBA macro that clears specific input cells, as the current script runs too slowly.

How to Speed Up an Excel Macro That Clears Input Cells
Product
Excel
Device & OS
not provided
Scenario
Optimizing a VBA macro designed to clear cell contents without lagging.
Observed behavior
The macro executes slowly because it loops through every single cell in the worksheet's UsedRange, evaluating formatting, merged cells, and intersections, which unnecessarily processes thousands of blank cells.
Before you start

Before modifying your macro script, save a backup copy of your Excel workbook (.xlsm). Press Ctrl+End on your worksheet to see the last cell Excel considers 'used', which will help you identify if your UsedRange is artificially bloated.

Solution 1Recommended

Use a Named Range Instead of UsedRange

Replacing the UsedRange loop with a specific Named Range allows the macro to instantly clear only the targeted cells without checking unnecessary rows and columns.

The 'UsedRange' property returns a range from the top-left cell to the bottom-right used cell. If a cell far down the sheet has formatting but no data, UsedRange will still include all empty cells in between, causing massive slowdowns in loop-based macros.

1
Define the target range

Select all the input cells on your worksheet that you want the macro to clear.

2
Create a Named Range

Click inside the Name Box located to the left of the formula bar, type a recognizable name like 'MyInputCells', and press Enter.

3
Open the VBA Editor

Press Alt + F11 to open the VBA Editor, then locate the module containing your clearing macro.

4
Update the macro code

Replace the 'UsedRange' looping code with a direct statement: Range("MyInputCells").ClearContents. For example: Sub ClearCells() Range("MyInputCells").ClearContents End Sub.

Use a Named Range Instead of UsedRange
Performance Boost: By directly targeting a named range, you eliminate the need for the macro to evaluate intersections and formats on a cell-by-cell basis, making execution near-instant.

Run and Optimize Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust built-in support for VBA macros. You can run, edit, and optimize your cell-clearing scripts effortlessly in WPS Spreadsheet, enjoying high performance and full compatibility with Microsoft Excel macro formats.

  1. 1. Open your macro file: Launch WPS Spreadsheet and open your existing .xlsm workbook.
  2. 2. Access the Developer tools: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor'.
  3. 3. Optimize your code: Apply the Named Range or ScreenUpdating techniques within the WPS VBA Editor.
  4. 4. Execute the macro: Run your macro to experience instantly cleared input cells with no lag.
Fully compatible with Microsoft Excel (.xlsm) macro-enabled filesBuilt-in VBA editor for easy script troubleshooting and optimizationFree and lightweight software ensuring fast execution speeds
microsoft office alternative - wps office

Frequently Asked Questions

Why does UsedRange include blank cells?

UsedRange defines a rectangular area spanning from the top-leftmost used cell to the bottom-rightmost used cell. If an otherwise empty cell contains hidden formatting, borders, or previously housed data that was deleted rather than cleared, Excel still counts it as 'used', expanding the range.

How do I check the actual UsedRange in my worksheet?

You can inspect your active UsedRange by pressing Ctrl + Home to see the starting point, and then Ctrl + End to jump to the last cell Excel considers modified. If Ctrl + End takes you to a blank area, your UsedRange contains unnecessary cells.

Does disabling screen updating really speed up macros?

Yes. By adding 'Application.ScreenUpdating = False' to the beginning of your code, you stop Excel from refreshing the application interface after every individual cell operation. This can reduce a macro's running time from minutes to mere seconds.