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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,078
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,144,364
Messages
5,723,939
Members
422,527
Latest member
TotalBeginner201

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