Record a macro when you select that range
Then Edit|goto (or hit ctrl-g or F5)
Click on the Special button
Select Constants, but only select Numbers
Type 0 and hit ctrl-enter
Stop recording the macro
You'll end up with code that looks like:
Range("c21:c31").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.FormulaR1C1 = "0"
You can change that to:
Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0
But if there are no number constants in that range, you'll get an error:
on error resume next
Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0
on error goto 0
Adding the worksheet to qualify the range and using a VBA constant to make it
easier to read:
on error resume next
worksheets("Sheet1").Range("c21:c31") _
.SpecialCells(xlCellTypeConstants, xlNumbers).value = 0
on error goto 0
wrote:
Hi,
I have this macro to reset cells in C21-C31 to 0.
However, how do I amend this macro that it will only re-set the cells
to 0 if it the cell contains a value greater then 0 as I do not wish
for the macro to add a 0 to cells that are empty with no cell values
in them at all.
Sub ResetAll()
Dim myRng As Range
Set myRng = Sheets("Sheet1").Range("C21:C31")
myRng.Value = "0"
End Sub
Is it possible to do this?
Thanks!
--
Dave Peterson