Clear contents on certain sheets

Crewchief521

New Member
Joined
Apr 14, 2019
Messages
18
I have a workbook that has about 500 sheets (all different names, but end with the same letter) , I want to create 9 buttons to clear the same cells in each sheet but choose only certain ones for each button. For instance, button 1 will clear about 80 sheets, button 2 will clear about 40 and so on.

Thanks in advance.
Denny
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
With list of sheets for each button maintained in sheet "Lists"
- column A for button1
- column B for button2 etc

You could use something like this to clear the ranges ...
Code:
Sub ClearCells(aList As Range)
    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("A3:B11,E7:F14,G17:O18").ClearContents
    Next
End Sub
And call it like this from each button ....
Code:
Sub Button[COLOR=#ff0000]1[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp)))
End Sub

Sub Button[COLOR=#ff0000]2[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("B1", Sheets("Lists").Range("B" & Rows.Count).End(xlUp)))
End Sub
 

Crewchief521

New Member
Joined
Apr 14, 2019
Messages
18
With list of sheets for each button maintained in sheet "Lists"
- column A for button1
- column B for button2 etc

You could use something like this to clear the ranges ...
Code:
Sub ClearCells(aList As Range)
    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("A3:B11,E7:F14,G17:O18").ClearContents
    Next
End Sub
And call it like this from each button ....
Code:
Sub Button[COLOR=#ff0000]1[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp)))
End Sub

Sub Button[COLOR=#ff0000]2[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("B1", Sheets("Lists").Range("B" & Rows.Count).End(xlUp)))
End Sub
When I click the button I get the error, wrong number of arguments or invalid property assignment.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
Without seeing your code I cannot tell what you have done wrong

Your error is most likely is in this line:
Code:
Sheets(cel.Value).Range([COLOR=#ff0000]"[/COLOR]A3:B11[COLOR=#ff0000],[/COLOR]E7:F14[COLOR=#ff0000],[/COLOR]G17:O18[COLOR=#ff0000]"[/COLOR]).ClearContents
It is a single string inside " "
Each range is separated by , (comma)

The error you describe would happen if you tried
Range("A3:B11", "E7:F14", "G17:O18")

If you cannot resolve it yourself, please post that line of code
 
Last edited:

Crewchief521

New Member
Joined
Apr 14, 2019
Messages
18
Code:
[COLOR=#333333][I]Sub ClearCells(aList As Range)[/I][/COLOR]    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("B4:B5").ClearContents
    Next [COLOR=#333333][I]End Sub[/I][/COLOR]
Code:
[COLOR=#333333][I]Sub Button[/I][/COLOR][COLOR=#ff0000][I]1[/I][/COLOR][COLOR=#333333][I]_Click()[/I][/COLOR]    Call ClearCells(Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp)))
End Sub

Sub Button[COLOR=#ff0000]2[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("B1", Sheets("Lists").Range("B" & Rows.Count).End(xlUp))) [COLOR=#333333][I]End Sub[/I][/COLOR]
That is my range on my test sheet i have here at home. i get the error when i click the button and it highlights the Call ClearCells(Sheets....) section.
 

Crewchief521

New Member
Joined
Apr 14, 2019
Messages
18
Code:
Sub ClearCells(aList As Range)
    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("I3,B7:B10,D7:D10,G7:G10,I7:I10,I13,I15,B19:B20,B13:B16,D13:D16,F19:F20,I19,I21").ClearContents
    Next
End Sub
That is my code, I get a subscript out of range error on the sheets(cel.value......) line. Any idea why?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
I have added to the code so that it hopefully runs without stopping and tells you something

Code:
Sub ClearCells(aList As Range)
    Dim cel As Range, msg As String
    For Each cel In aList
        On Error Resume Next
        Sheets(cel.Value).Range("I3,B7:B10,D7:D10,G7:G10,I7:I10,I13,I15,B19:B20,B13:B16,D13:D16,F19:F20,I19,I21").ClearContents
        If Err.Number > 0 Then msg = msg & vbCr & cel.Value
        On Error GoTo 0
    Next
    MsgBox msg
End Sub
My immediate guess would be a typo in one or more sheet names in your list (ie VBA is choking on a non-existent sheet name)
If so, run the amended code the message box will tell you all you need to know
 
Last edited:

Crewchief521

New Member
Joined
Apr 14, 2019
Messages
18
I have added to the code so that it hopefully runs without stopping and tells you something

Code:
Sub ClearCells(aList As Range)
    Dim cel As Range, msg As String
    For Each cel In aList
        On Error Resume Next
        Sheets(cel.Value).Range("I3,B7:B10,D7:D10,G7:G10,I7:I10,I13,I15,B19:B20,B13:B16,D13:D16,F19:F20,I19,I21").ClearContents
        If Err.Number > 0 Then msg = msg & vbCr & cel.Value
        On Error GoTo 0
    Next
    MsgBox msg
End Sub
My immediate guess would be a typo in one or more sheet names in your list (ie VBA is choking on a non-existent sheet name)
If so, run the amended code the message box will tell you all you need to know
It runs but the message box is blank. I went thru and edited my table to not be longer than needed, to remove blanks.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
Why have blank cells in a list of sheets? If the cell values are blank then they are listed in the message box but are invisible! My message box idea was not robust enough
Hopefully they are now all removed
You could add a nonsense name at the bottom to see if it is picked up

To pick up everything including blank cells
Amend the message string to this and the cell address will show
Code:
msg = msg & vbCr & cel[COLOR=#ff0000].Address(0,0)[/COLOR] & " " & cel.Value
If the message box is still empt,y then you have eliminated your problem and can prove it by putting an apostrophe in front of On Error Resume Next (I would then re-instate the line to help if you have a mismatch in the future)
 
Last edited:

Forum statistics

Threads
1,085,968
Messages
5,387,053
Members
402,032
Latest member
kylaanderson83

Some videos you may like

This Week's Hot Topics

Top