CODEWITHSUNDEEP
vbaexcel
adam
adam

Reputation: 424

VBA Remove 100k + named ranges

So basically I've gotten a legacy excel from my predecessor to process a bunch of data. He basically names each datapoint and process them accordingly. The problem is that his codes does not clear away the old used names, resulting in names accumulating through the years. Right now I've got 100k + names in my excel sheet that I can't clear.

I've tried using:

Sub dlname 

  Dim nm as name 

  For each nm in activeworkbook.names

    nm.delete

  Next nm 

End sub

It'll always give me runtime error 7, out of memory.

Any solutions to this ?

Upvotes: 1

Views: 4835

Answers (2)

Charles Williams
Charles Williams

Reputation: 23505

I suggest you do it in chunks with (as Amorpheuses says) a save in between

    Sub dlname

    Dim j as long

    For j=20000 to 1 step -1
     if j<=activeworkbook.names.count then
       activeworkbook.names(j).delete
     end if
    Next j
    activeworkbook.save
    End sub

Upvotes: 0

areklipno
areklipno

Reputation: 528

try this:

Do While (ActiveWorkbook.Names.Count > 0)

ActiveWorkbook.Names.Item(1).Delete

Loop

Upvotes: 2

Related Questions