Very slow when I delete Row...how to speed up?

[leungkong]

I use the following code to delete some row.
In Excel 2003, it is very fast. (less than 10 second)
But in excel 2007, i save the file type .xlsm, the code is very very
slow...(more than 3 mins)
I find that "Rows(i).Delete" is very slow if I use screenupdate mode.
1 second delete 2 rows only....

How can I speed up this process?
Thanks in advance.


Private Sub DeleteS()
Dim i As Long, LastRow As Long
Dim Counter As Integer
LastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 6 Step -1
If Range("F" & i) = "S" Then
Rows(i).Delete
Counter = Counter + 1
End If
Next i
MsgBox Counter & " row(s) is/are deleted"
End Sub

 

[Gary Keramidas]

next to your screenupdating = false add

Application.Calculation = xlCalculationManual

and at the end of your code turn it back on

Application.Calculation = xlCalculationAutomatic

 

[JLGWhiz]

Try setting calculation to manual, see if that helps. Then set it back to
automatic at the end of the macro.
Application.Calculation = xlCalculationManual 'Top of macro
Application.Calculation = xlCalculationAutomatic 'Bottom of macro

 

[JLGWhiz]

You can also set EnableEvents to False at the top of the macro and then
enable them again at the bottom.

EnableEvents = False
'Your code
EnableEvents = True

 

[leungkong]

Thanks, but it's still very slow...

You can also set EnableEvents to False at the top of the macro and then
enable them again at the bottom.

EnableEvents = False
'Your code
EnableEvents = True

 

[Jim Cone]

Maybe column A extends far below column F?...
'--
Private Sub DeleteS()
Dim i As Long, LastRow As Long
Dim Counter As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Sheets("Data")
LastRow = .Cells(.Rows.Count, 6).End(xlUp).Row
For i = LastRow To 6 Step -1
If .Cells(i, 6).Value = "S" Then
.Rows(i).Delete
Counter = Counter + 1
End If
Next i
End With
MsgBox Counter & " row(s) is/are deleted "
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA


"leungkong"
<[email protected]>
wrote in message
I use the following code to delete some row.
In Excel 2003, it is very fast. (less than 10 second)
But in excel 2007, i save the file type .xlsm, the code is very very
slow...(more than 3 mins)
I find that "Rows(i).Delete" is very slow if I use screenupdate mode.
1 second delete 2 rows only....
How can I speed up this process?
Thanks in advance.

Private Sub DeleteS()
Dim i As Long, LastRow As Long
Dim Counter As Integer
LastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 6 Step -1
If Range("F" & i) = "S" Then
Rows(i).Delete
Counter = Counter + 1
End If
Next i
MsgBox Counter & " row(s) is/are deleted"
End Sub

 

[JLGWhiz]

You might try disc cleanup and defragmentation if you haven't done that in a
while. Those are available in control panel under performance and
maintenance.

 

[ytayta555]

        If Range("F" & i) = "S" Then
            Rows(i).Delete

Use method ClearContent . It work faster
then Delete .

Rows(i).ClearContent

( or ClearContents , I don't remember now )

 

[Chip Pearson]

Rather than deleting the rows one by one within the loop, use a Range
type variable to save references to all the rows to be delete and then
once the loop is complete, delete the rows in one single operation. In
the code below, the variable RR is set within the For loop to point to
all the rows to be deleted, and once the loop is complete, the Delete
method is called on RR, deleting all the rows in one operation.

Sub AAA()
Dim i As Long, LastRow As Long
Dim Counter As Integer
Dim RR As Range
On Error GoTo ErrH:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

LastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 6 Step -1
If Range("F" & i) = "S" Then
If RR Is Nothing Then
Set RR = Rows(i)
Else
Set RR = Application.Union(RR, Rows(i))
End If
Counter = Counter + 1
End If
Next i
RR.Delete
MsgBox Counter & " row(s) is/are deleted"
ErrH:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ron de Bruin]

Hi leungkong

I have a few examples here
http://www.rondebruin.nl/delete.htm

Try the Union or filter example

 

[leungkong]

It is faster. Thanks.
But, i find that xls format is faster than xlsm format a lot.
I try to use the same file to save as xls format.
the speed is very fast. (less than a second)