How to get rid of these dead macros?

[musicgold]

Hi,

Please see the attached file. This file has some dead macros which I have been trying to remove. This is just one sheet from a large file. The large file is there in my department for years.
As you will notice that there is no code module in the file and nor is there a way to view these macros. The only way to see them in Excel is by using Tools à Macro à Macros…

I want these macros out has someone as assigned the typical keyboard shortcuts to these macros and they are hampering my work style. For example, Ctr + S has been assigned to Macro 5. As a result I can’t use Ctr+S to save my work and that is very annoying. I tried to re-assign the shortcuts but it didn’t work.

I also tried the code given on this page, but even it didn’t work and I got the following message: the VBProject is protected or has no components.

Is there a way to get rid of these macros or at least re-assign the shortcut keys?

Thanks.

[Bob Phillips]

I think the macros are in hidden sheets that we are not seeing, maybe XLM sheets.

[musicgold]

Thanks. But, I just copied this one sheet from the main file into this new workbook.

Also, how do I see the XLM sheets?

[Bob Phillips]

I am suggesting post the whole workbook.

[Paul_Hossler]

Control-F3 to edit named ranges

Click the first one, scroll to the end of the (very long) list and shift - click the last to select them all

Click Delete

Control-S seems to save the WB. I didn't test any other

Paul

[musicgold]

Thanks folks.

Control-S seems to save the WB

Yes! What a relief.

I am suggesting post the whole workbook.

I can't post the whole file for privacy reasons. But I don't understand one thing here. I just copied the data sheet in a new workbook using the 'move or copy' method and the macros still got transferred to the new file. That means those macros are somehow connectd to that sheet. It beats me.

[Paul_Hossler]

if you look at the names, there's a Macro03 with a Refers to =Data!Macro03

I deleted all the names from the WB you posted, copied Data to new WB, and there were no macros in the copy

Paul

[Jan Karel Pieterse]

Note that there is also a number of hidden range names in your file which are corrupt (VBA cannot remove them).

[Aussiebear]

I wasn't aware you could hide range names. How is this done and naturally how do you locate them?

[Jan Karel Pieterse]

It is done by VBA code. I located them using my Name Manager add-in

[Aussiebear]

I have found 10 macros.... is that all or have you found more?

[Jan Karel Pieterse]

That was all indeed.