How to make this code work faster?!? Please help

Vera

Banned
Joined
Sep 22, 2002
Messages
33
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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)
 

Vera

Banned
Joined
Sep 22, 2002
Messages
33
Only on the selected range and should NOT delete any formula (=3-3=0 or A4-A5=0)
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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>
 

Vera

Banned
Joined
Sep 22, 2002
Messages
33
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
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
I don't understand how it does that either, because it should only be running in cells you've selected. Hmmm.
 

Forum statistics

Threads
1,144,451
Messages
5,724,425
Members
422,553
Latest member
excelgraham

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top