Error Message when trying to clear contents on multiple sheets

Marv S

New Member
Joined
Jun 13, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
I'm new to this and I'm trying to clear cells on multiple sheets that some cells have formulas and it works fine unless there is a sheet that has nothing in the cells that were selected, it then gives me the following error. Please help! Thanks!

Run-time Error '1004":
No cells were found

On debug the highlighted line is on the sheet that did not have any information, see sheet "E" the line of code
VBA Code:
Selection.SpecialCells(xlCellTypeConstants, 23).Select
. This is what my code looks like. By the way I have sheets that run thru "U".

VBA Code:
Sub ClearTimeContents()
'
' ClearTimeContents Macro
'
    Sheets("Time").Select
    ActiveSheet.Unprotect
    Range("D5:U1764").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
    Range("D5").Select
    
    Sheets("D").Select
    ActiveSheet.Unprotect
    Range("C35:C494").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
    Range("C35").Select
    
    Sheets("E").Select
    ActiveSheet.Unprotect
    Range("C35:C494").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
    Range("C35").Select
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,318
VBA Code:
Dim i As Long

With Sheets("Time")
    .Unprotect
    On Error Resume Next
    .Range("D5:U1764").SpecialCells(xlCellTypeConstants).ClearContents
    On Error Goto 0
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:= False
End With

For i = Asc("D") to Asc("U")
    With Sheets(Chr(i))
        .Unprotect
        On Error Resume Next
        .Range("C35:C494").SpecialCells(xlCellTypeConstants).ClearContents
        On Error Goto 0
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:= False
    End With
Next i
 

Forum statistics

Threads
1,181,427
Messages
5,929,828
Members
436,697
Latest member
sunnypl

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
Top