macro to delete all labels

  • Thread starter Thread starter Lynn
  • Start date Start date
If by 'labels' you mean any text, then this will do it

Sub tryme()
For Each mycell In Application.ActiveSheet.UsedRange
If Application.WorksheetFunction.IsText(mycell) Then
mycell.Clear
End If
Next
End Sub

best wishes
 
Lynn,

Depends on what you mean by labels.... if all strings, then

Sub DeleteAllLabels()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Cells.SpecialCells(xlCellTypeConstants, 2).Clear
Next sh
End Sub

HTH,
Bernie
MS Excel MVP
 
If you mean comments then run this from each worksheet:-

Public Sub ClearLabels()
For Each c In ActiveSheet.Comments
c.Delete
Next
End Sub

If you want to do all sheets within the workbook then post back and I will
update.
 
If by "labels" you mean Label controls (from either the Forms toolbar or the
Control Toolbox toolbar), then read on.

Your question wasn't clear about whether you wanted every label on every
worksheet deleted or only the labels on a specific worksheet. Here is the
code for removing the labels from *all* worksheets...

Sub DeleteAllLabelControls()
Dim WS As Worksheet
Dim Lbl As OLEObject
For Each WS In Worksheets
WS.Labels.Delete
For Each Lbl In WS.OLEObjects
Lbl.Delete
Next
Next
End Sub

Here is how to remove them from a single worksheet (assumed to be named
Sheet1 for this example)...

Sub LabelControlsFromSheet1()
Dim Lbl As OLEObject
With Worksheets("Sheet1")
.Labels.Delete
For Each Lbl In .OLEObjects
Lbl.Delete
Next
End With
End Sub
 
sorry, its called names.

Insert -> names -> define

How do i delete all names in a workbook. thanks
 
Public Sub RemoveNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Delete
Next
End Sub
 
You might want to be careful when deleting *all* names since Excel uses
Names for some of the things it does (Print Area, Print Titles for example);
however, this code will delete *all* Names...

Sub deleteRanges()
Dim WBname As Name
For Each WBname In ActiveWorkbook.Names
WBname.Delete
Next
End Sub
 
That was not the best macro name I could have used<g> (it was the name I had
on another routine; I just cleared out its code and wrote my new code in its
"housing"). Perhaps

Sub DeleteNames()

might be a better name to use.
 
There are names that excel creates and uses. You may not want to delete them.

I'd get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You'll be able to delete the names you want and keep the names you're not sure
of.
 
Back
Top