Excel - convert values to zero [Resolved]

[salvelinus]

I want to select a range in a worksheet (B6:AZ1000 should cover all the ranges in any worksheet) and if there's a number in the cell, change it to zero. There are no formulas, text, etc; the cell will either be blank or have a number, but I want to keep the blank cells blank. This is in order to convert existing worksheets into templates.
I can do this manually by copying a cell with a zero, selecting the entire worksheet or range, then use PasteSpecial Values. But how can I do this in code? And can I make this code a part of Excel rather than having to put it into every worksheet? Thanks.

[dglienna]

You could record a macro, and after you end it, edit the code in the macro, which you could then use (after slight modification) in a vb program that would call each workbook that you want to modify.

[si_the_geek]

Here is a macro that will set the cell value to zero if it currently contains a number (this will work for all cells on the worksheet). You can paste this into a code window for the workbook you want to store it in.

Sub SetValuesToZero()

Dim objCell As Range
For Each objCell In ActiveSheet.UsedRange
If objCell.Value <> "" Then
If IsNumeric(objCell.Value) Then
objCell.Value = 0
End If
End If
Next objCell

End Sub

NB: This will ignore any cells which contain anything other than a number.


And can I make this code a part of Excel rather than having to put it into every worksheet? Thanks.
As long as the worksheet that it is stored in is open, you can run the macro. I doubt that you will want to run this everytime you have Excel open, so I would recommend saving it to a "common macros" workbook (which you can put other macros into later), and just load that workbook whenever you want to use it.

It is possible to store macros in a hidden workbook which always opens with Excel, but doing this will slow down Excel slightly - so it is best to avoid it.

[salvelinus]

Thanks, I'll give that a try later today. Do a lot more work in Access than Excel.