Visible Cell .EntireRow.Delete 2010

[JimS]

The following code runs in <2 seconds with Excel-2007.

Runnning the same code using Excel-2010 it takes >60 seconds to run.

This line: Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete

I checked and the lastrow is equal in 2007 & 2010 (about 18,000).

Any ideas why this runs much slower with 2010?

Thanks...

JimS



Sub Delete_Types2()
Dim lastrow As Long
Worksheets("Data1").Select
ActiveSheet.Range("AB:AB").AutoFilter
' Filters on column-AB for PE & PS on the Data1 sheet
ActiveSheet.Range("AB:AB").AutoFilter Field:=1, Criteria1:=PE", Operator:=xlOr, Criteria2:="=PS"
' Deletes visible filtered rows
lastrow = Cells(Rows.Count, 10).End(xlUp).Row
If lastrow > 1 Then
Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
End Sub

[mdmackillop]

This may help if you have dependent cells, but it still takes longer that I would expect. I've left in the timer function.

Sub Delete_Types3()
Dim lastrow As Long
Dim tim
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("Data1").Select
' Filters on column-AB for PE & PS on the Data1 sheet
ActiveSheet.Range("AB:AB").AutoFilter Field:=1, Criteria1= "=PE", Operator:=xlOr, Criteria2:="=PS"
' Deletes visible filtered rows
lastrow = Cells(Rows.Count, 10).End(xlUp).Row
tim = Timer
If lastrow > 1 Then
Rows("2:" & lastrow).Delete
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Timer - tim
ActiveSheet.Range("AB:AB").AutoFilter
End Sub

[SamT]

Sub SamT_Delete_Types2()
Dim lastrow As Long
Dim i As Long
Dim Strt
Strt = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("Data1")
lastrow = .Cells(Rows.Count, 10).End(xlUp).Row
With .Range("AB:AB")
For i = lastrow To 2 Step -1
If .Cells(i) = "PE" Or .Cells(i) = "PS" Then _
.Cells(i).EntireRow.Delete
End If
Next i
End With
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Timer - Strt
End Sub

[JimS]

Thanks for your responses.

mdmackillop solutions runs in less than a second, SamT yours takes over 2 minutes.

I need to uses this procedure several times - do you know of any reason that it runs very quickly the first time used but much slower when used again when filtering on different criteria? Should I be clearing cache (or something)?

JimS

[SamT]

2 minutes?!?!

Wow!

Just how many data rows do you have?

Why so long? Maybe Column AB contains Formulas? If so, my code might speed up if you used

.Cells(i).Text =
Or
.Cells(i).Value =
If you put

Cells(LastRow, "AB").Select
MsgBox ""
after MC's "LastRow=" Line, the App will pause until you click OK, allowing you to see the result of the filtering. Note that you can't scroll the sheet while the MsgBox is showing.

[mdmackillop]

Hi Jim
Can you post your code showing the how the other filters are implemented

[JimS]

I failed to mention that the second time through I was using a "Filter-in-Place", not just a straight filter - not sure if that would have an affect on how the rows.delete would work.

I was able to greatly reduce the time by sorting the data based on the filter criteria just before the "Rows("2:" & lastrow).Delete" runs. It only takes about a second now to delete the rows.

There is just way too much confidential data to share this one out.

SamT,
One of the later sections of code that was taking so long is deleting >11,000 rows out of 19,000 to answer your question.
Also, there are not any formulas.

Not sure if this is the best resolution, but it certainly works now.

Thanks to both of you for your help.

JimS

[SamT]

:beerchug: