VBA & Macro Problems

How to Delete Thousands of Excel Named Ranges Using VBA

Tauseeq Magsi Sep 28, 2026 871 views

Question details

The user needs to delete a massive number of named ranges from a workbook without exhausting system memory or causing the application to crash.

How to Delete Thousands of Excel Named Ranges Using VBA

Product: Excel
Device & OS: not provided
Scenario: Attempting to clean up an overloaded workbook containing tens or hundreds of thousands of named ranges.
Observed behavior: Trying to delete all named ranges at once causes the application to run out of memory or crash.

Before you start

Before running the macro, press Ctrl + F3 to open the Name Manager to verify the scale of the unwanted named ranges, and close any other heavy background applications to free up system memory.

Solution 1Recommended

Delete Named Ranges in Batches via VBA

Running a batch-deletion macro removes chunks of named ranges sequentially, which prevents memory exhaustion.

When a workbook accumulates over 100,000 named ranges, a single loop execution can exceed the software's memory limit. Executing the deletion in batches of 10,000 is a much safer approach.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu, then select 'Module' to open a blank code window.

Paste the Batch Deletion Code

Copy and paste the following code: Sub DeleteNamesBatch() Dim wb As Workbook, i As Long, limit As Long; Set wb = ThisWorkbook; limit = 10000; For i = Application.Min(limit, wb.Names.Count) To 1 Step -1; On Error Resume Next; wb.Names(i).Delete; On Error GoTo 0; Next i; End Sub.

Run the Macro

Press F5 or click the 'Run' button. Repeat this process multiple times until all unwanted named ranges are fully removed.

Save Between Batches: Because deleting over 100,000 names is resource-heavy, manually save your workbook after every few batch runs to ensure your progress is preserved.

Solution 2

Delete All Named Ranges at Once

Use this straightforward macro if your workbook contains a moderate, manageable number of named ranges.

Efficient Spreadsheet Management

Manage Workbooks and VBA Macros Smoothly with WPS Office

WPS Office provides comprehensive support for Excel VBA macros, allowing you to easily run cleanup scripts like batch deleting named ranges without sacrificing performance. It offers a lightweight, fully functional spreadsheet environment for heavy data tasks.

1. Open Your Workbook: Launch WPS Spreadsheets and open your .xlsm or .xlsx file.
2. Access the VBA Editor: Navigate to the 'Tools' tab on the top ribbon and click on 'Macro' to open the built-in VBA Editor.
3. Run the Cleanup Script: Insert a new Module, paste your batch deletion VBA script, and press 'Run' to easily clear out the thousands of named ranges.

Highly compatible with Microsoft Excel (.xlsx, .xlsm, .xls) macro-enabled files.Native support for advanced VBA scripts to automate your workbook cleanup.Lightweight architecture ensures smoother performance, reducing the risk of crashes.Familiar user interface that requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I have so many named ranges in my workbook?

Massive numbers of named ranges are often generated accidentally when copying and pasting data, or duplicating sheets between different workbooks over a long period. Hidden names can also accumulate from older, imported data.

Can I delete named ranges without using VBA?

Yes, you can go to the Formulas tab and open the Name Manager to delete them. However, manually selecting and deleting tens of thousands of named ranges often causes the software to freeze or crash, making VBA the more reliable choice.

What does 'limit = 10000' do in the VBA code?

It sets a cap on the number of named ranges the macro will attempt to delete in a single execution. Limiting it to 10,000 prevents the application's memory from overloading during the process.

Excel & Data Problems