Because it's not named Print_Area, but something like
Sheet1!Print_Area.
Try:
If Instr(Activeworkbook.Names(R),"Print_Area")=0
then
Activeworkbook.Names(R).Delete
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED
OR WRITTEN BY KPMG TO BE USED, AND CANNOT BE USED, BY A
CLIENT OR ANY OTHER PERSON OR ENTITY FOR THE PURPOSE OF (i)
AVOIDING PENALTIES THAT MAY BE IMPOSED ON ANY TAXPAYER OR
(ii) PROMOTING, MARKETING OR RECOMMENDING TO ANOTHER PARTY
ANY MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions specifically set forth herein and is based on
the completeness and accuracy of the stated facts,
assumptions and/or representations included. In rendering
our advice, we may consider tax authorities that are subject
to change, retroactively and/or prospectively, and any such
changes could affect the validity of our advice. We will not
update our advice for subsequent changes or modifications to
the law and regulations, or to the judicial and
administrative interpretations thereof.
The advice or other information in this document was
prepared for the sole benefit of KPMG's client and may not
be relied upon by any other person or organization. KPMG
accepts no responsibility or liability in respect of this
document to any person or organization other than KPMG's
client.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G PEACH.EASE.LSOFT.COM]
On Behalf Of Strong, Pauline N.
Sent: Wednesday, March 07, 2007 2:39 PM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Deleting Named Ranges Except for Print_Area
I searched the archives, and I found that I asked this once
before, but
I
didn't see an answer. Is it possible to delete all ranges in
a worksheet
except for Print_Area with VBA?
I did try
For R = 1 To ActiveWorkbook.Names.Count
If ActiveWorkbook.Names <>"Print_Area"
ActiveWorkbook.Names(R).Delete
Next
End If
This still deletes Print_Area, so I am using
Private Sub cmdClear_Click()
Range("c7:ar50").ClearContents
Range("k2:ar5").ClearContents
Range("bb54:bb63").ClearContents
'Set nms = ActiveWorkbook.Names
For R = 1 To ActiveWorkbook.Names.Count
ActiveWorkbook.Names(R).Delete
Next
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:= _
"=R1C2:R50C45"
End Sub
This doesn't work because there is something wrong with my
R1C1 range
syntax, but I really want not to delete Print_Area in the
first place
instead of adding it back. I would settle for adding it back
if
necessary,
and I would like to know how to name an R1C1 range and how
to add a name
without using R1C1. (Help shows Names.add with R1C1, which
is why I used
it,
but in help, it is only for a single cell.)
Thanks in advance for any advise.
- Pauline
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
<html>
<body>
<p>***************************************************
********************</p>
<p>The information in this email is confidential and
may be legally privileged.
It is intended solely for the addressee. Access to this
email by anyone else is
unauthorized. If you are not the intended recipient, any
disclosure, copying,
distribution or any action taken or omitted to be taken in
reliance on it, is
prohibited and may be unlawful. When addressed to our
clients any opinions or
advice contained in this email are subject to the terms and
conditions
expressed in the governing KPMG client engagement
letter.</p>
<p>***************************************************
********************</p>
</body>
</html>
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|