Excel 2003: VBA: How to Speed Up Row Deletions

[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

 

[Jim Cone]

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

 

[Guest]

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

 

[Dave Peterson]

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

 

[Ron de Bruin]

I have add a few things here that will help
http://www.rondebruin.nl/delete.htm

 

[Guest]

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???

 

[Ron de Bruin]

Hi Jim

I never test it with very big files Jim but I think your code is faster ?

 

[Guest]

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...