Excel VBA Slow Loop

[MSingh]

Hi,

Plse advise how can i speed up this loop.

Sub Delete_0()
'Purges Listings of "0" Values
Dim Cell As Range
For Each Cell In Range("A5:F3008")
If Cell.Value = 0 Then
Cell.Value = ""
'Cell.ClearContents is equally slow
End If
Next Cell
End Sub

Thanks in Advance
Mohamed

[HansV]

It is much faster to replace 0 with "" in one go:

Range("A5:F3008").Replace What:=0, Replacement:="", LookAt:=xlWhole

For extra speed, turn off screen updating, events and automatic calculation temporarily:

Code: Select all

Sub Delete_0()
  'Purges Listings of "0" Values
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  Range("A5:F3008").Replace What:=0, Replacement:="", LookAt:=xlWhole
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

[MSingh]

Thanks Hans,

That's excellent!

Kind Regards
Mohamed