VBA to clear multiple ranges from list of sheets

[y0rk1e72]

Let me explain.
How do i get a macro/vba to look at a list of sheets in one worksheet and depending on the data to the right of the name clear the correct range(s).
for example list would look like
staff M5, D14:E14
manager D9:E39, G44:I49
director B44:E49

so using the above i'd need to look at the name, i.e staff and it would clear M5, followed by the range D14:E14 then would look and the next name which would be manager and clear D9:e39 followed by G44:I49. it would then look at the next name in the list which using the above would be director.

The sheets are hidden and have merged cells within the range to be cleared.

is this even possible?
any help/pointers would be greatly appreciated.​

 

[Ken Puls]

The way I would do it is:

Set up a named range to cover each of those areas. I.e:
-Select cells M5 and D14:E14 (at the same time), and name that range as rngStaff
-Name your other two ranges rngManager and rngDirector respectively

You'd then clear it with the following macro:

Sub Clear
With Worksheets("Sheet1")
     .Range("rngStaff").ClearContents
     .Range("rngManager").ClearContents
     .Range("rngDirector").ClearContents
End With
End Sub

The good news then is that you only have to keep the names up to date in the main Excel interface and they will continue to work, even if someone inserts ranges above.

 

[Simon Lloyd]

How about:

     .Range("rngStaff,rngManager,rngDirector").ClearContents

or

.Range("M5, D14:E14, D9:E39, G44:I49, B44:E49").ClearContents

 

[y0rk1e72]

How about:

     .Range("rngStaff,rngManager,rngDirector").ClearContents

or

.Range("M5, D14:E14, D9:E39, G44:I49, B44:E49").ClearContents

Both excellent answers why i didn't think of naming a range and having it clear
thanks for that

 

[Ken Puls]

Hey Simon,

For what it's worth...

     .Range("rngStaff,rngManager,rngDirector").ClearContents

I usually separate each named range into a separate code line. It's personal preference only, but I like to have each line declared explicitly. To me it makes them a bit more obvious and self documenting in th code. You can certainly argue that, of course.

With regards to:

.Range("M5, D14:E14, D9:E39, G44:I49, B44:E49").ClearContents

I always avoid this format and always name the range. Again, this will work, but I like the "future proofing" of the named ranges. When I was first learning to code, and before I knew what I was doing, I burned myself a few times by inserting rows, not realizing the VBA code wouldn't update.

 

[Simon Lloyd]

Hi Ken i agree on both counts, i was just showing the OP other avenues, one thing that merits mention is that with named ranges, if you dont make them dynamic then inserting data within the named range wont give you expected results, a great explanation here by Helen http://www.contextures.com/xlNames01.html should help folk understand better.