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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,259
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
5,259
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
5,259
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
5,259
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,089,483
Messages
5,408,530
Members
403,212
Latest member
Rvbicon

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top