Macro to clear values only
Posted by Jim on April 13, 2001 6:40 AM
To use an existing monthly worksheet as a template for a new month, I would like a macro that will check a fixed range (A4:GZ67) and clear all cells that contain a value. All cell formats and cells with formulas or text must remain unchanged. How would this most easily be accomplished?
Posted by Ivan Moala on April 13, 2001 7:28 AM
Jim, this macro may help you
Sub Clear_Range()
Dim myRg As Range
Dim vRg As Range
Set myRg = Range("A4:GZ67")
On Error Resume Next
Set vRg = myRg.SpecialCells(xlCellTypeConstants, 23)
If Err = 0 Then
vRg.ClearContents
End
Else
Set myRg = Nothing
Set vRg = Nothing
MsgBox "No values, or values already cleared!"
End If
Ivan
Posted by Jim on April 13, 2001 8:05 AM
The above code clears text as well as values. Otherwise, this is what I needed.
Posted by Ivan Moala on April 13, 2001 3:18 PM
Jim
change the line;
Set vRg = myRg.SpecialCells(xlCellTypeConstants, 23)
TO
Set vRg = myRg.SpecialCells(xlCellTypeConstants, 21)
Ivan
