clear ranges

[Striker]

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

 

[Don Guillett]

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

 

[Stefi]

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:

 

[Don Guillett]

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

 

[Stefi]

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

Stefi

„Don Guillett†ezt írta:

 

[Striker]

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

 

[Patrick Molloy]

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