PDA

View Full Version : [SOLVED] Why Is my code so painfully slow?



Saladsamurai
11-25-2009, 07:28 AM
Is the '.Clear' method what is slowing me down? I need to clear a cell if it contains a "c"

If it contains a "c" it also has blue format from some conditional formatting that the workbook came to me with.

I know that there are quite a few cells for it to check (108,000) but I know Excel can do better than this. It's taking minutes, and even then, my PC stops responding.

Attached worksheet.


Option Explicit
Dim i As Long
Dim j As Long
Dim nRow As Long
Dim nCol As Long


Sub DeleteCoolers()
nRow = 2000
nCol = 56
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 1 To nRow
For j = 1 To nCol
'Already checked 1st and Last 2 slots of each cluster manually-->no coolers in those spots
If Cells(i + 3, j + 2) = "c" Then
Cells(i + 3, j + 2).Clear
End If
Next j
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


EDIT: can't upload....making me wonder why this file is so big....

tpoynton
11-25-2009, 07:40 AM
Perhaps replace is quicker? used macro recorder to get the code...

Option Explicit

Sub DeleteCoolers()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Range(Cells(1, 1), Cells(2000, 56)).Replace What:="c", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

mbarron
11-25-2009, 07:48 AM
Make sure you change the
LookAt:=xlPart to LookAt:=xlWhole
otherwise all of your C's will be gone. You'll have oolers instead of coolers

Saladsamurai
11-25-2009, 07:53 AM
Thanks for the suggestions. I just removed the conditional formatting and then used the .Clear method instead of .ClearContents. Much faster!