Activex command button to clear contents

[sloany101]

I need some help , i feel i am very close , i have 31 sheets and have multiple separate ranges of cells i am looking to clear with an activex command button.

Below is a line of code that actually works very well.

Private Sub CommandButton1_Click()
If MsgBox("Are you sure? Cannot undo.", vbYesNo) = vbNo Then Exit Sub
'Updateby Extendoffice

Sheet2.Range("B7:T18").ClearContents
Sheet2.Range("B21:T32").ClearContents
Sheet2.Range("B40:T51").ClearContents
Sheet2.Range("B54:T65").ClearContents
Sheet2.Range("B73:T84").ClearContents
Sheet2.Range("B87:T98").ClearContents
Sheet2.Range("B107:T118").ClearContents
Sheet2.Range("B121:T132").ClearContents

Sheet3.Range("B7:T18").ClearContents
Sheet3.Range("B21:T32").ClearContents
Sheet3.Range("B40:T51").ClearContents
Sheet3.Range("B54:T65").ClearContents
Sheet3.Range("B73:T84").ClearContents
Sheet3.Range("B87:T98").ClearContents
Sheet3.Range("B107:T118").ClearContents
Sheet3.Range("B121:T132").ClearContents
End Sub

BUT , what i want to do is for each sheet's array of cells (IE: B7:T18 , B21:T32, ETC) consolidate them to one line of code (instead of 8 lines of code for each sheet), to something like this

Private Sub CommandButton1_Click()
If MsgBox("Are you sure? Cannot undo.", vbYesNo) = vbNo Then Exit Sub
'Updateby Extendoffice
Sheets("Sheet1").Array.Range("B7:T18", "B21:T32", "B40:T51", "B54:T65", "B73:T84", "B87:T98", "B107:T118", "B121:T132").ClearContents
Sheets("Sheet2").Array.Range("B7:T18", "B21:T32", "B40:T51", "B54:T65", "B73:T84", "B87:T98", "B107:T118", "B121:T132").ClearContents
ETC
End Sub

If anyone can help me out i would greatly appreciate it , i feel i am close , ALSO note the above consolidated code when used states "Run-time error '9': Subscript out of range"

 

[Domenic]

Try it like this...

Sheet2.Range("B7:T18,B21:T32,B40:T51,B54:T65,B73:T84,B87:T98,B107:T118,B121:T132").ClearContents

Actually, it looks like your sheets are named Sheet1, Sheet2, . . . Sheet31. If so, you can re-write your code as follows...

    Dim i As Long
    For i = 1 To 31
        ThisWorkbook.Worksheets("Sheet" & i).Range("B7:T18,B21:T32,B40:T51,B54:T65,B73:T84,B87:T98,B107:T118,B121:T132").ClearContents
    Next i

Hope this helps!

 

[sloany101]

Try it like this...

Sheet2.Range("B7:T18,B21:T32,B40:T51,B54:T65,B73:T84,B87:T98,B107:T118,B121:T132").ClearContents

Actually, it looks like your sheets are named Sheet1, Sheet2, . . . Sheet31. If so, you can re-write your code as follows...

    Dim i As Long
    For i = 1 To 31
        ThisWorkbook.Worksheets("Sheet" & i).Range("B7:T18,B21:T32,B40:T51,B54:T65,B73:T84,B87:T98,B107:T118,B121:T132").ClearContents
    Next i

Hope this helps!

The first one worked perfectly , tbh i tried the 2nd one once and it wasnt working , but im ok with using the first one , it works fine , thank you very much !!

 

[Domenic]

You're very welcome, I'm glad I could help.

Although, if you confirm how your sheets are named, I can easily provide you with the correct code.

Cheers!

 

[sloany101]

You're very welcome, I'm glad I could help.

Although, if you confirm how your sheets are named, I can easily provide you with the correct code.

Cheers!

the sheets are named as you guessed its basically 1-31 , its a spreadsheet used for entire months that we reuse every month and instead of having to go in and manually clear all the cells was looking to streamline the process , and your code worked well and helped me cut a lot of time out in writing it thanks again .

 

[Domenic]

Just to be clear, is it 1, 2, 3, ... 31 ? Or Sheet1, Sheet2, Sheet3, ... Sheet31?

Also, we're talking sheet names, not code names, right?

 

[sloany101]

Just to be clear, is it 1, 2, 3, ... 31 ? Or Sheet1, Sheet2, Sheet3, ... Sheet31?

Also, we're talking sheet names, not code names, right?

Sheet1 .... etc to Sheet31 , correct .

 

[Domenic]

Then the code should work, unless . . .

1) One or more sheets do not exist, in which case you'll get a subscript out of range error.

2) One or more sheets are protected, and the target cells are locked, in which case you'll get a cells are protected error.

3) The sheets do not exist in the workbook running the code, but rather the active workbook, in which case you can use the following instead...

ActiveWorkbook.Worksheets("Sheet" & i).Range("B7:T18,B21:T32,B40:T51,B54:T65,B73:T84,B87:T98,B107:T118,B121:T132").ClearContents

If you continue to have problems, please clarify. And, if you get an error, please confirm which line gives you the error, and the type of error it gives you.