VBA multiple button to clear multiple ranges

[APML]

Hi all, I have a worksheet that I have broken down into 50 sections, within these sections I have various unprotected cells that I need to clear.

i.e In section 1, I need to clear the range I've called C_1 which refers to =Watch!XEQ11,Watch!XEV11,Watch!XEW11,Watch!XEY11,Watch!XEZ11,Watch!XEQ14,Watch!XET14,Watch!XEU14,Watch!XEV14,Watch!XEW14,Watch!XEX14,Watch!XEQ17,Watch!XEZ16,Watch!XEY18,Watch!XEZ18,Watch!XEW27,Watch!XEY27,Watch!XEW30,Watch!XEY30,Watch!XEQ32,Watch!XEQ34,Watch!XER34,Watch!XES34,Watch!XET34,Watch!XEU34

In section 2, I need to clear the range I've called C_2 which refers to
=Watch!A1,Watch!F1,Watch!G1,Watch!I1,Watch!J1,Watch!A4,Watch!D4,Watch!E4,Watch!F4,Watch!G4,Watch!H4,Watch!A7,Watch!J6,Watch!I8,Watch!J8,Watch!G17,Watch!I17,Watch!G20,Watch!I20,Watch!A22,Watch!A24,Watch!B24,Watch!C24,Watch!D24,Watch!E24

Etc, all the way up to 50. I will be using a form button to trigger the clear contents (so I'll have 50 button, 1 per section)

So my question is, do I need 50 separate VBA codes or can I have 1 vba that references the various buttons, .....am thinking something like if the button in C_1 is triggered then it would clear range C_1 contents

 

[Michael M]

Don't have Excel at the moment but maybe this way

Sub MM1()
Dim r As Integer
 For r = 1 To 50
     Range("C_" & r).ClearContents
 Next r
End Sub

OR

Sub MM2()
Dim nName As Name
    For Each nName In Names
        If InStr(1, nName.Name, "C_") > 0 Then
            nName.ClearContents
        End If
    Next nName
End Sub

 

[APML]

Firstly, thx for your help. I'm not very good at VBA, so pls excuse me if my question sounds silly,
How do I actually trigger the above?
i.e I create the above code but how does the button in say section 2 (which clears range C_2) actually trigger the VBA?

 

[Michael M]

Just assign the code to 1 button.
It will then do ALL Ranges in one run

 

[APML]

Just assign the code to 1 button.
It will then do ALL Ranges in one run

So i only want button 1 to clear range C_1 (not all ranges), and button 2 to clear range C_2 etc up to 50

 

[Michael M]

So you just want

Sub C_1()
  Range("C_1").ClearContents
End Sub

 

[APML]

So you just want

Sub C_1()
  Range("C_1").ClearContents
End Sub

So do i need to repeat the above 50 times , as in 50 macros and assign them to the specific buttons?

 

[Michael M]

yes and the number in "C_??) needs to change for each one. As well as the name of the Sub.....Sub C_1()....Sub("C_2")), etc.
But why not simply put an input box in the code asking the user which range to clear....then you only need 1 button. AND 1 code

Sub C_1()
ans = InputBox("Which Range do you wish to clear?")
  Range(ans).ClearContents
End Sub

 

[APML]

yes and the number in "C_??) needs to change for each one. As well as the name of the Sub.....Sub C_1()....Sub("C_2")), etc.
But why not simply put an input box in the code asking the user which range to clear....then you only need 1 button. AND 1 code

Sub C_1()
ans = InputBox("Which Range do you wish to clear?")
  Range(ans).ClearContents
End Sub

thats a really great idea. Is there anyway that instead of inputing the named range it would bring up a pulldown list from which it could be chosen?

 

[Michael M]

Yep, you could use a ComboBox list, but with 50 items of very similar name, I can imagine you would possibly selct the wrong item !!!
Have a look here at creating combobox lists

Excel ComboBox Lists for Easy Data Entry

How to use an Excel ComboBox with a drop down list to make data entry easy in an Excel UserForm

www.contextures.com