#### Vera

##### Banned
I use this code to delete cells that have zero in them (constant zero NOT the result of a formula); so the code will not delete numbers<> 0, formulas (even if the result is zero), or text. My only problem is that it takes very long for this code to check every cell in my selection, which is always different and large. Please can you help me out to speed up this macro?

Thank you so much.

Sub deletezero()

Dim myselection As range
Set myselection = Selection
On Error Resume Next
For Each c In myselection
If (WorksheetFunction.IsNumber(c) And Not (c.HasFormula) And c.FormulaR1C1 = "0") Then
c.Value = ""
ElseIf (c.HasFormula Or WorksheetFunction.IsText(c)) Then
c.Formula = c.Formula
End If
Next c
End Sub
This message was edited by Vera on 2002-10-14 17:25

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Do you just want this macro to run on the selected range, or would it be just as easy to find all of the "0" values on the worksheet to remove them.

I guess what I'm trying to say is, is it desirable to have "0" in a cell in any part of the worksheet outside of the selection?

(I have picked up that you don't want to delete values that are the result of a formula e.g. 3-3 = 0)

Only on the selected range and should NOT delete any formula (=3-3=0 or A4-A5=0)

Something like this might be faster, however I don't have a dataset large enough to really test it. Out of interest, how many cells do you typically select to run this macro on?

<pre>
Sub deletezero()

Dim MySelection As Range
Dim c As Range

Set MySelection = Selection

With MySelection.SpecialCells(xlCellTypeConstants)
Set c = .Find(What:=0, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With

End Sub</pre>

Thank you Mark, yours is faster, 3 times faster; but I don't understand how it selects the area? I mean if I forget to select my area, the macro knows to go to the area with info; but if I have 3 adjiacent areas with info, and I want to clear just one area and I forget to select it, this code will clear all 3!!

I forgot; I guess a few thousands; for example now from CR250 to A1; sometimes more or less and not the whole range.
This message was edited by Vera on 2002-10-14 18:37

I don't understand how it does that either, because it should only be running in cells you've selected. Hmmm.

Replies
16
Views
623
Replies
3
Views
284
Replies
3
Views
284
Replies
17
Views
837
Replies
2
Views
204

1,217,386
Messages
6,136,292
Members
450,002
Latest member
bybynhoc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back