So, I’m using a macro to automate a tedious data manipulation process on a large data set. As part of this I take a pre-existing calculated sheet, let’s say “Output”, and create a new sheet called “Output_Final” which will contain the updated data.
I check to see if “Output Final” exists, and if it does I delete and re-create it. This works fine in terms of physically replacing the actual sheet. However in the object browser I see a heap of phantom Sheet objects (apologies for the rubbish picture quality):
I’m deleting sheets using the following method, although the sheet name is parameterised:
ActiveWorkbook.Sheets("Output_Final").Delete
Am I missing something obvious? I’m still developing this solution so there will be a heap more test runs to do, and users will re-run frequently so I don’t really want hundreds of phantom objects hanging around. Apart from anything else the initial default sheet name being generated increments each time and I don’t know if there’s a limit.
Amanensia is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
This can happen if the objects aren’t fully released from memory. After deleting the sheet, explicitly set the sheet object to Nothing to release it from memory:
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Output_Final")
ws.Delete
Set ws = Nothing
See also Remove a non-existant Sheet in VBA, VBA code deletes it’s own sheet, leaving a “ghost” sheet behind, GHOST Worksheet in Project Explorer – How to Delete It?
3