hi,

anyone has a macro to delete all labels in a spreadsheet?

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Lynn" wrote in message
...
hi,

anyone has a macro to delete all labels in a spreadsheet?

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


"Lynn" wrote in message
...
hi,

anyone has a macro to delete all labels in a spreadsheet?

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.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Lynn" wrote:

hi,

anyone has a macro to delete all labels in a spreadsheet?

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

--
Rick (MVP - Excel)


"Lynn" wrote in message
...
hi,

anyone has a macro to delete all labels in a spreadsheet?

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Lynn" wrote:

sorry, its called names.

Insert - names - define

How do i delete all names in a workbook. thanks

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

--
Rick (MVP - Excel)


"Lynn" wrote in message
...
sorry, its called names.

Insert - names - define

How do i delete all names in a workbook. thanks

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Lynn" wrote in message
...
sorry, its called names.

Insert - names - define

How do i delete all names in a workbook. thanks

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.

Lynn wrote:

sorry, its called names.

Insert - names - define

How do i delete all names in a workbook. thanks

--

Dave Peterson