Delete Contents of Named Ranges(s)

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I have a XLS Workbook with multiple Worksheets, with those worksheets I have
multiple Named Ranges. I will like to be able to do a "mass deletion" of all
values that all named ranges contain. Can this be done, if so how?

Thanks,
Anthony
 
try this idea where rnga and rnge are each separate named ranges

Sub emptyrngs()
Range("rnga,rnge").ClearContents
End Sub
 
Didn't work..
Don Guillett said:
try this idea where rnga and rnge are each separate named ranges

Sub emptyrngs()
Range("rnga,rnge").ClearContents
End Sub
Click to expand...
 
sure did. I created two ranges, named them, put something in them, executed
the macro and the contents were cleared. Perhaps your problem is different.
 
There is a Lotus command (key sequence) that deletes every name in the WB
and can be used in XL when you set the transition navigation keys.

This was originally posted by Norman Harker and is supposed to be the best
kept secret of XL (so they tell me).

I've used it several times and it does perform as advertised.

<Tools> <Options> <Transition> tab,
Check "TransitionNavigationKeys"
*AND*, as Dave Peterson had to point out to me,
Check "Lotus 1-2-3 Help",
Then <OK>.

NOW, anywhere in the WB:
/RNR

Ignore the Lotus Help window that is opened by the "slash",
And type the RNR.
And watch your cursor move around the sheet by itself and get rid of all
your names.

Don't forget to switch back after you're done.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Didn't work..
 
You didn't mention your version.
I've been told that this *doesn't* work in XL03!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

There is a Lotus command (key sequence) that deletes every name in the WB
and can be used in XL when you set the transition navigation keys.

This was originally posted by Norman Harker and is supposed to be the best
kept secret of XL (so they tell me).

I've used it several times and it does perform as advertised.

<Tools> <Options> <Transition> tab,
Check "TransitionNavigationKeys"
*AND*, as Dave Peterson had to point out to me,
Check "Lotus 1-2-3 Help",
Then <OK>.

NOW, anywhere in the WB:
/RNR

Ignore the Lotus Help window that is opened by the "slash",
And type the RNR.
And watch your cursor move around the sheet by itself and get rid of all
your names.

Don't forget to switch back after you're done.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Didn't work..
 
If that is what is desired then these might help. The first one is what I
use

Sub DeleteAllNames()
For Each Name In Names
Name.Delete
Next Name
End Sub

Sub DeleteAllNames_Umlas()
ExecuteExcel4Macro "SUM(DELETE.NAME(NAMES()))"
End Sub

Sub RemoveReferences()
Dim ws As Worksheet
Dim n As Name
For Each ws In Worksheets
ws.Cells.Copy
ws.Range("A1").PasteSpecial xlPasteValues
Next ws
For Each n In ActiveWorkbook.Names
n.Delete
Next n
End Sub
 
Thanks to all of you for your help! These Named Rnages are actually Tables
that I have linked through an Access DB, but due to the ISAM issues Access
won't allow me to run a script to delete the values. I'm not too enthused
about creating a macro because this XLS is forwarded monthly to an end
customer. Is there a way to exclude the macro once the end user forwards the
XLS on?

Thanks again
 
Anthony Viscomi said:
These Named Rnages are actually Tables
that I have linked through an Access DB, but due to the ISAM issues Access
won't allow me to run a script to delete the values.
Click to expand...

There is a workaround. The ISAM supports DROP TABLE and CREATE TABLE
so if you don't need to retain any data it will be simple to recreate
your tables using a SQL script.

--
 
Back
Top