Excel 2003: VBA: How to Speed Up Row Deletions
 

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

Excel 2003: VBA: How to Speed Up Row Deletions
 

One thing that could help is...
ActiveSheet.DisplayPageBreaks = False
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"BEEJAY"
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

Excel 2003: VBA: How to Speed Up Row Deletions
 

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

Jim Thomlinson


"BEEJAY" wrote:

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

Excel 2003: VBA: How to Speed Up Row Deletions
 

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

BEEJAY wrote:

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

--

Dave Peterson

Excel 2003: VBA: How to Speed Up Row Deletions
 

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BEEJAY" 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

Excel 2003: VBA: How to Speed Up Row Deletions
 

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

Jim Thomlinson


"Ron de Bruin" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BEEJAY" 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

Excel 2003: VBA: How to Speed Up Row Deletions
 

Hi Jim

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Thomlinson" wrote in message
...
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???
--
HTH...

Jim Thomlinson


"Ron de Bruin" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BEEJAY" 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

Excel 2003: VBA: How to Speed Up Row Deletions
 

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

Jim Thomlinson


"Ron de Bruin" wrote:

Hi Jim

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Thomlinson" wrote in message
...
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???
--
HTH...

Jim Thomlinson


"Ron de Bruin" wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"BEEJAY" 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