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

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

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

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