1

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 ?

Amorpheuses
  • 1,403
  • 1
  • 9
  • 13
adam
  • 414
  • 2
  • 8
  • 21
  • 1
    See this [post](http://stackoverflow.com/questions/14396998/how-to-clear-memory-to-prevent-out-of-memory-error-in-excel-vba) (ie save the workbook periodically). – Amorpheuses May 17 '17 at 07:29
  • i've added activeworkbook.save after nm.delete. it still shows me the same error. (am i doing this right?) i think excel died at loading the name list. is there a way to break down the name list and delete them piecemeal? – adam May 17 '17 at 07:42

2 Answers2

2

try this:

Do While (ActiveWorkbook.Names.Count > 0)

ActiveWorkbook.Names.Item(1).Delete

Loop
areklipno
  • 538
  • 5
  • 11
0

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
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • Code logic is wrong: `j` is incremented and compared against a falling limit as `.names.count` is dynamic. Of `n` names, `(n-1)/2` names will be left undeleted. Besides, the loop is only run through once, so there's only 1 chunk. @areklipno's code uses `.names.count` correctly and should be marked as the answer. – user1016274 May 17 '17 at 10:27