To avoid this "shifting problem", I prefer the
ForEach loop:
Dim N As Name
For Each N In ActiveWorkbook.Names
If N.Name Like "*!Print_Area" Then N.Delete
Next N
Kind regards,
Eddy Opperdoes
-----Oorspronkelijk bericht-----
Van: MS Excel General Q & A List [mailto:EXCEL-G PEACH.EASE.LSOFT.COM]
Namens Umlas, Robert
Verzonden: woensdag 7 maart 2007 21:03
Aan: EXCEL-GPEACH.EASE.LSOFT.COM
Onderwerp: Re: Deleting Named Ranges Except for Print_Area
Try
For R = ActiveWorkbook.Names.Count To 1 Step -1
If InStr(ActiveWorkbook.Names(R).Name,
"Print_Area") = 0 Then
ActiveWorkbook.Names(R).Delete
End If
Because when you delete names(3), for example, names(4)
becomes names(3)
etc and they all move up one. Also, you need
Activeworkbook.Names(R).Name, as shown above
*** 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-GPEACH.EASE.LSOFT.COM]
On Behalf Of Strong, Pauline N.
Sent: Wednesday, March 07, 2007 2:55 PM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Deleting Named Ranges Except for Print_Area
Bob,
I tried
Private Sub cmdClear_Click()
Range("c7:ar50").ClearContents
Range("k2:ar5").ClearContents
Range("bb54:bb63").ClearContents
For R = 1 To ActiveWorkbook.Names.Count
If InStr(ActiveWorkbook.Names(R),
"Print_Area") = 0 Then
ActiveWorkbook.Names(R).Delete
End If
Next
End Sub,
And it still deleted the print range!
On Behalf Of Umlas, Robert
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
On Behalf Of Strong, Pauline N.
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
---------------
Aan de inhoud van dit e-mailbericht kunnen geen rechten
worden ontleend.
De informatie verzonden in dit e-mailbericht is uitsluitend
bestemd voor de geadresseerde. Het Centraal Bureau voor de
Statistiek staat niet in voor de juiste en volledige
overbrenging van de inhoud van een verzonden e-mailbericht
noch voor tijdige ontvangst daarvan.
No rights may be derived from the contents of this e-mail
message.
The information in this e-mail message is intended only for
the addressee. Statistics Netherlands cannot vouch for the
correctness and completeness of the contents of e-mail
messages, nor for the timely receipt thereof.
------------------------------------------------------------
--------------
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
|