VBA to clear multiple ranges from list of sheets

y0rk1e72

New member
Joined
Nov 7, 2012
Messages
2
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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:

Code:
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
How about:
Code:
     .Range("rngStaff,rngManager,rngDirector").ClearContents
or
Code:
.Range("M5, D14:E14, D9:E39, G44:I49, B44:E49").ClearContents
 

y0rk1e72

New member
Joined
Nov 7, 2012
Messages
2
Reaction score
0
Points
0
How about:
Code:
     .Range("rngStaff,rngManager,rngDirector").ClearContents
or
Code:
.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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hey Simon,

For what it's worth...
Code:
     .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:
Code:
.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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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.
 
Top