clear ranges

  • Thread starter Thread starter Striker
  • Start date Start date
S

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
 
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:
 
OR this with no loop and no selections.
with Range("a1:b2,c4:d5,etc")
.ClearContents
.ClearComments
end with
 
Or with names:
With Range("name1, name2, etc.")

Stefi

„Don Guillett†ezt írta:
 
OK, this works, I just need to be sure it only happens on the current
worksheet, not workbook.
 
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
 
Back
Top