Excel 2003: VBA: How to Speed Up Row Deletions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Looking for suggestion on how to change the following as required,
to greatly improve speed of operation.
Without the following: about 5-6 seconds
WITH the following: about 30 seconds.

Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "x" Then
Rows(RowNdx).Delete
End If
Next RowNdx
 
One thing that could help is...
ActiveSheet.DisplayPageBreaks = False
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"BEEJAY" <[email protected]>
wrote in message
Looking for suggestion on how to change the following as required,
to greatly improve speed of operation.
Without the following: about 5-6 seconds
WITH the following: about 30 seconds.

Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "x" Then
Rows(RowNdx).Delete
End If
Next RowNdx
 
The fastest way is to use Find as opposed to traversing each cell
individually. Also you want to do all of the deletes at once instead of one
row at a time... Something like this...

Sub DeleteStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("B")
Set rngFound = rngToSearch.Find(What:="x", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry nothin found"
Else
strFirstAddress = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Delete
End If
End Sub
 
Turning calculation to manual, hiding the pagebreaks, and changing to normal
view can increase the speed.

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub
 
Question for you Ron. I was under the impression that doing a find / find
next with a union was about the fastest way to delete... Your site shows some
code for doing the delete via filtering. Which one is faster???
 
Hi Jim

I never test it with very big files Jim but I think your code is faster ?
 
I'll have to test it (in my spare time... I'm freakin busy)... but as a guess
it should be close enough make no practical difference...
 
Back
Top