Clear Contants w/o effecting formulas

C

cparsons

Thanks for the help.

I create the following macro but it seems to be working funny.

With Worksheets(Cur_Month).Range("a7")
ThisWorkbook.Worksheets(Cur_Month).Activate

Range(Cur_Month & "!All_Data").Select

Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents

End With

It was working great then I saved / closed / and reopen my applicatio
now it gets to the Selection.SpecialCells(xlCellTypeConstants
23).Select, part, I get an error saying "No Cells were found".

What does that mean? I verified that the range I want selected i
selected but I still get that error
 
M

Myrna Larson

It means that you've already cleared the constants. This sub won't work again
without error until you actually enter more constant data.

Looks like you recorded this, which is fine. Here's what it should look like
after you clean it up. I added the On Error statement to handle the case when
there are no cells with constants on the worksheet.

Sub ClearConstants
On Error Resume Next
ThisWorkbook.Worksheets(Cur_Month).Range("All_Data")_
.SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub
 
Top