Delete Macro

  • Thread starter Thread starter Jimmy
  • Start date Start date
J

Jimmy

Hi,

For normal case, delete a macro is simple. Just press
the "delete button" in macro menu.

However, for the macro that has no reference and
the "delete button" is hidden. How can I delete macro in
this case.
 
Go to the Visual Basic editor. All macro code resides in this area. Macro
recorded code usually goes into what's called a module. You'll see these
listed under the modules branch of the tree on the left side. Double click
each of these, find the macro's you want to delete and just delete the text.

However all this said if you're new to VBA etc. it's easy to get a bit lost
in here.


--

Regards,


Bill Lunney
www.billlunney.com
 
Jimmy,

You really shouldn't send emails to people without their
permission. I rarely ever open emails from people I don't
know that contain attachments. But I had a look at the
file you sent anyway. Grief dude! What a mess. It has
one sheet, with no formulas or data at all in it, and yet
you have over 500 defined names, and 17 external links,
and none of them seem to be valid (#REF errors all over
the place). Three of these defined names are showing up
as macros.

Macro Calc_Power_Rate is actually a defined name refering
to ='C:\Sim''s Project\Singapore\SPT SDH Access\[SPT Core
Network Equipment List with Pricing.xls]#REF'!$B$48

fill_nes2 is ='C:\Sim''s Project\Singapore\SPT SDH
Access\[SPT Core Network Equqipment List with Pricing.xls]
#REF'!#REF!

deployment_housekeeping is ='C:\Sim''s
Project\Singapore\SPT SDH Access\[SPT Core Network
Equipment List with Pricing.xls]#REF'!$B$992

I don't know if you need to keep any of these, so this
macro will list all the names in your book. Run it on a
blank sheet, have a look to see what you need to keep and
what can be removed.

Sub ListNames()
For a = 1 To ActiveWorkbook.Names.Count
Cells(a, 1).Value = ActiveWorkbook.Names(a).Name
Cells(a, 2).Value = ActiveWorkbook.Names(a)
Next
End Sub


If you need to keep any, you'll have to manually remove
the rest. But if you can remove all of them, then use
this macro:

Sub RemoveNames()
On Error Resume Next
For a = ActiveWorkbook.Names.Count To 1 Step -1
ActiveWorkbook.Names(a).Delete
Err.Clear
Next
End Sub

I used the error trap, because some of the names create an
error when I tried to delete them programmatically. The
names themselves are invalid because they don't use
standard characters (boxes as part of the name?). After
you run this then you'll have to manually delete 5 or 6
names that can't be removed with the macro.
Insert/Name/Define, and then go to town.
 
Mike,

Sorry about to sent you E-mail directly. I will not sent
to you again but post it on the news group.

Regarding your macro, it is very useful. I have deleted
all the name inside the workbook. And the macro name also
disappear. When I use Insert/Name/Define to delete the
name, 2 of them can not be deleted. Have you face this
problem.

Regards,
Jimmy

-----Original Message-----
Jimmy,

You really shouldn't send emails to people without their
permission. I rarely ever open emails from people I don't
know that contain attachments. But I had a look at the
file you sent anyway. Grief dude! What a mess. It has
one sheet, with no formulas or data at all in it, and yet
you have over 500 defined names, and 17 external links,
and none of them seem to be valid (#REF errors all over
the place). Three of these defined names are showing up
as macros.

Macro Calc_Power_Rate is actually a defined name refering
to ='C:\Sim''s Project\Singapore\SPT SDH Access\[SPT Core
Network Equipment List with Pricing.xls]#REF'!$B$48

fill_nes2 is ='C:\Sim''s Project\Singapore\SPT SDH
Access\[SPT Core Network Equqipment List with Pricing.xls]
#REF'!#REF!

deployment_housekeeping is ='C:\Sim''s
Project\Singapore\SPT SDH Access\[SPT Core Network
Equipment List with Pricing.xls]#REF'!$B$992

I don't know if you need to keep any of these, so this
macro will list all the names in your book. Run it on a
blank sheet, have a look to see what you need to keep and
what can be removed.

Sub ListNames()
For a = 1 To ActiveWorkbook.Names.Count
Cells(a, 1).Value = ActiveWorkbook.Names(a).Name
Cells(a, 2).Value = ActiveWorkbook.Names(a)
Next
End Sub


If you need to keep any, you'll have to manually remove
the rest. But if you can remove all of them, then use
this macro:

Sub RemoveNames()
On Error Resume Next
For a = ActiveWorkbook.Names.Count To 1 Step -1
ActiveWorkbook.Names(a).Delete
Err.Clear
Next
End Sub

I used the error trap, because some of the names create an
error when I tried to delete them programmatically. The
names themselves are invalid because they don't use
standard characters (boxes as part of the name?). After
you run this then you'll have to manually delete 5 or 6
names that can't be removed with the macro.
Insert/Name/Define, and then go to town.

-----Original Message-----
Hi,

For normal case, delete a macro is simple. Just press
the "delete button" in macro menu.

However, for the macro that has no reference and
the "delete button" is hidden. How can I delete macro in
this case.
.
Click to expand...
.
Click to expand...
 
In your case this is true. You have a stack of incorrect named ranges.

Look at:

http://localhost/Excel/FAQ/DisplayFAQ.ascx?ExcelFAQID=203

for more details.





This is likely to be due to having an invalid named reference/s setup.
Choose Insert > Name > Define from the worksheet menu and look through the
relevent named ranges. Chances are your trying to run a macro in a file
which is not accessible.

Delete these references and save the file. It's important to save then
reload it as Excel doesn't seem to update the macro dialog properly until
this is done.


--

Regards,


Bill Lunney
www.billlunney.com
 
Back
Top