I have about 40 named ranges in a sheet. I need do quickly do a ,loop and
clear all named ranges. currently I am clearing like this, but it is
getting too long to do with all the ranges. not sure how to assing a range
name to a variable and clear it.

With ActiveSheet

Range("ONE").Select
Selection.ClearContents
Selection.ClearComments

Range("TWO").Select
Selection.ClearContents
Selection.ClearComments

Range("THREE").Select
Selection.ClearContents
Selection.ClearComments
end with

Perhaps a sample with actual ranges may be of help in designing a loop

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Striker" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>I have about 40 named ranges in a sheet. I need do quickly do a ,loop and
>clear all named ranges. currently I am clearing like this, but it is
>getting too long to do with all the ranges. not sure how to assing a range
>name to a variable and clear it.
>
> With ActiveSheet
>
> Range("ONE").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("TWO").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("THREE").Select
> Selection.ClearContents
> Selection.ClearComments
> end with

Try something like this:
Sub test()
Dim n As Name
For Each n In ThisWorkbook.Names
Range(n).ClearContents
Range(n).ClearComments
Next n
End Sub

Regards,
Stefi

„Strikerâ€� ezt Ã*rta:

> I have about 40 named ranges in a sheet. I need do quickly do a ,loop and
> clear all named ranges. currently I am clearing like this, but it is
> getting too long to do with all the ranges. not sure how to assing a range
> name to a variable and clear it.
>
> With ActiveSheet
>
> Range("ONE").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("TWO").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("THREE").Select
> Selection.ClearContents
> Selection.ClearComments
> end with
>
>

OR this with no loop and no selections.
with Range("a1:b2,c4:d5,etc")
.ClearContents
.ClearComments
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Striker" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>I have about 40 named ranges in a sheet. I need do quickly do a ,loop and
>clear all named ranges. currently I am clearing like this, but it is
>getting too long to do with all the ranges. not sure how to assing a range
>name to a variable and clear it.
>
> With ActiveSheet
>
> Range("ONE").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("TWO").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("THREE").Select
> Selection.ClearContents
> Selection.ClearComments
> end with

Or with names:
With Range("name1, name2, etc.")

Stefi

„Don Guillettâ€� ezt Ã*rta:

> OR this with no loop and no selections.
> with Range("a1:b2,c4:d5,etc")
> .ClearContents
> .ClearComments
> end with
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Striker" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> >I have about 40 named ranges in a sheet. I need do quickly do a ,loop and
> >clear all named ranges. currently I am clearing like this, but it is
> >getting too long to do with all the ranges. not sure how to assing a range
> >name to a variable and clear it.
> >
> > With ActiveSheet
> >
> > Range("ONE").Select
> > Selection.ClearContents
> > Selection.ClearComments
> >
> > Range("TWO").Select
> > Selection.ClearContents
> > Selection.ClearComments
> >
> > Range("THREE").Select
> > Selection.ClearContents
> > Selection.ClearComments
> > end with
>
>

OK, this works, I just need to be sure it only happens on the current
worksheet, not workbook.


"Stefi" <(E-Mail Removed)> wrote in message
news:36545525-3FA8-4BD9-AC4B-(E-Mail Removed)...
> Try something like this:
> Sub test()
> Dim n As Name
> For Each n In ThisWorkbook.Names
> Range(n).ClearContents
> Range(n).ClearComments
> Next n
> End Sub
>
> Regards,
> Stefi
>
> "Striker" ezt írta:
>
>> I have about 40 named ranges in a sheet. I need do quickly do a ,loop
>> and
>> clear all named ranges. currently I am clearing like this, but it is
>> getting too long to do with all the ranges. not sure how to assing a
>> range
>> name to a variable and clear it.
>>
>> With ActiveSheet
>>
>> Range("ONE").Select
>> Selection.ClearContents
>> Selection.ClearComments
>>
>> Range("TWO").Select
>> Selection.ClearContents
>> Selection.ClearComments
>>
>> Range("THREE").Select
>> Selection.ClearContents
>> Selection.ClearComments
>> end with
>>
>>

Option Explicit
Sub clearRanges()
clearSheetRanges "sheet2"
End Sub

Sub clearSheetRanges(sh As String)
Dim nm As Name
Dim rng As Range
For Each nm In ThisWorkbook.Names
Set rng = Range(nm)
If UCase(rng.Parent.Name) = UCase(sh) Then
Worksheets(sh).Range(nm).ClearContents
End If
Next
End Sub

"Striker" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> I have about 40 named ranges in a sheet. I need do quickly do a ,loop and
> clear all named ranges. currently I am clearing like this, but it is
> getting too long to do with all the ranges. not sure how to assing a
> range name to a variable and clear it.
>
> With ActiveSheet
>
> Range("ONE").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("TWO").Select
> Selection.ClearContents
> Selection.ClearComments
>
> Range("THREE").Select
> Selection.ClearContents
> Selection.ClearComments
> end with