Certain parts of code to clear cells is not working

dsrt16

Board Regular
Joined
Jun 18, 2005
Messages
208
I have a reset button on a user forum to reset the workbook to its original state. Certain parts of the code do not work, and I am not sure why. I have placed the two sections of the code that doesn't work in comments.



FIRST half of the code. Problem #1: It clears everything on sheet1, but then the debugger runs when it gets to sheet2, and gives a range error.

Code:
Private Sub CommandButton1_Click()
    Sheet1.Range("B5:C34").Select
    Selection.ClearContents
    Sheet1.Range("E10, E12, E14, E16").Select
    Selection.ClearContents
    Sheet1.Range("B5").Select

    'Sheet2.Range("C5:E12").Select
    'Selection.ClearContents
    'Sheet2.Range("C5").Select

Second half of the code. Problem #2: It will place the formula on sheets 3-32, but it will not clear the contents in the cells.

Code:
    Dim i As Integer

    For i = 3 To 32
        Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"
        'Sheets(i).Range("C8:C29").Select
        'Selection.ClearContents
    Next i
    
    Unload Me
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try this:
Code:
 Sheet1.Range("B5:C34").ClearContents
 Sheet1.Range("E10, E12, E14, E16").ClearContents
    
 Sheet2.Range("C5:E12").ClearContents
and this.
Code:
    Dim i As Integer
    For i = 3 To 32
        Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"
        Sheets(i).Range("C8:C29").ClearContents
    Next i
    
    Unload Me
 
Upvote 0
Code:
Private Sub CommandButton1_Click()     
Sheets(1).Range("B5:C34").ClearContents     
Sheets(1).Range("E10, E12, E14, E16").ClearContents     
Sheets(1).Range("B5").Select      
Sheet(2).Range("C5:E12").ClearContents     
Sheet(2).Range("C5").Select
Code:
Dim i As Integer      
For i = 3 To 32        
Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"         
Sheets(i).Range("C8:C29").Select        
Selection.ClearContents    
Next i          
Unload Me
End Sub
 
Last edited:
Upvote 0
Thanks Norie that worked. I am sure machopicho's code would have worked too.

I have always done .select and then selection.clearcontents, which I clearly don't need to. But I am curious why the .select and selection.clearcontents would would for sheet1 and then it would have a problem on sheet2.

Norie, your code is just less wording, but it is saying the same thing. Because I am learning, I want to know why it didn't work the way I had it.
 
Upvote 0
To tell you the truth I don't really know, or more accurately can't explain very well.

It's something to do with not being able to select a worksheet and range at the same time unless the worksheet is active.

So if Sheet1 is active, this is fine,
Code:
Sheet1.Range("A1:A10").Select
Selection.ClearContents

but this won't work because Sheet2 isn't the active sheet.
Code:
Sheet2.Range("Z1:X100000").Select
Hope that might make it clearer, but it probably won't.:eek:

As for the code I posted saying the same thing, sort of but not quite.:)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top