Select multiple named ranges and clear contents

[colindickson]

Morning,


Is there an easier way to code this?



Sheets("ABC").Select
Range("ABC").Select
Selection.ClearContents
Range("A1").Select


Sheets("BCD").Select
Range("BCD").Select
Selection.ClearContents
Range("A1").Select




I basically have multiple named ranges that I want to clear the contents of, effectively starting a fresh... do I have to loop through every sheet and named range?


Thanks

[georgiboy]

Hi Colin,

You could loop through the named ranges and check the named range name and clear the contents:


Sub nmLoop()
Dim nm As Name
For Each nm In ThisWorkbook.Names
If Right(nm.Name, 1) = 3 Then
Range(nm.Name).ClearContents
End If
Next nm
End Sub

Or just refer to the named ranges and clear them:


Sub nmRngClear()
Range("range1,range2,range3").ClearContents
End Sub

Hope this helps

[p45cal]

refer to the named ranges and clear them:

Sub nmRngClear()
Range("range1,range2,range3").ClearContents
End SubI don't think you'll be able to do that if the ranges are on different sheets. As georgiboy says, looping is the way to go.
You seem to know exactly which named ranges you want to clear, so you can loop through them:
Sub blah()
For Each RngName In Array("ABC", "BCD") 'add to/adjust this list
Range(RngName).ClearContents
Next RngName
End Sub
There'a no selecting going on so you don't need select A1 afterwards each time, the code won't change what's currently selected.