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 Sub

I 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.