Macro which enter zeros is slow; need help to speed it up
Posted by Veronica P. on February 13, 2001 9:48 AM
I created this macro that in myselection will find cells that do not have formulas or text, but only numbers and if the number is <>0 it will replace the number with zeros.
I do not know what is wrong with it but it is very slow; even if I select only 100 cells with probably only 10 cells with formulas and 20 cells that are not zero, it takes for ever to run. Can it be optimized? Thanks for any help.
Sub enterzero()
Dim myselection As range
Set myselection = Selection
On Error Resume Next
For Each c In myselection
If WorksheetFunction.IsNumber(c) And Not c.HasFormula Then
c.Value = 0
ElseIf (c.HasFormula Or WorksheetFunction.IsText(c)) Then
c.Formula = c.Formula
End If
Next c
End Sub
Posted by Mark W. on February 13, 2001 11:00 AM
Veronica, why don't you just use this?
Sub enterzero()
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.FormulaR1C1 = "0"
End Sub
Posted by Veronica P on February 13, 2001 11:19 AM
But this will not enter zeros in cells that have formulas or text also? I want to put zero only where I have numbers and to leave untouched cells that have text or formulas.
Posted by Mark W. on February 13, 2001 11:26 AM
> I want to put zero only where I have numbers
> and to leave untouched cells that have text
> or formulas.
...and what makes you think that this doesn't
fulfill your needs? Did you try it?
Posted by Veronica P on February 13, 2001 11:49 AM
I'm sorry Mark , it works perfectly & is so fast thank you
Posted by Dave Hawley on February 13, 2001 11:46 PM
Veronica, Mark is right! But rather than using a Recorded macro, you could simply use:
On Error Resume next
Selection.SpecialCells(xlCellTypeConstants, 1) = 0
Dave
OzGrid Business Applications
