Macro not working in 2007

[olympiac]

Hi,
I installed the Excel 2007 on my pc and the following code doesn't work anymore. I have got a debug at the following row:
.Range("H1:H" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)

Any suggestions?
Thanks

CODE:
Sub Delete_with_Autofilter_Array1()
Dim Rng As Range
Dim calcmode As Long
Dim myArr As Variant
Dim I As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'Fill in the values that you want to delete
myArr = Array("product1", "product2")

For I = LBound(myArr) To UBound(myArr)
'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet
'Firstly, remove the AutoFilter
.AutoFilterMode = False
'Apply the filter
.Range("H1:H" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
Set Rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not Rng Is Nothing Then Rng.EntireRow.Delete
End With
'Remove the AutoFilter
.AutoFilterMode = False
End With
Next I
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[pboltonchina]

Try this on a copy of your data

      Dim LR As Long, i As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("H" & i).Value = "product1" Or Range("H" & i).Value = "product2" Then Rows(i).Delete
Next i

 

[olympiac]

Thanks for your answer. I'll try it next Monday and let you know.
Cheers

 

[olympiac]

The code works fine however I have noticed that the time to run the macro 2007 is longer than 2003. Is it due to the fact that have got a long list of "products"?


If Range("H" & i).Value = "product1" Or Range("H" & i).Value = "product2" ...."product20"</pre>
Is there a way to speed it by adding an array?
Thanks

 

[olympiac]

Solved. Here is the solution:

Dim Arr As Variant

Arr = Array("product1", "product2", "product3", "product4")
With ActiveSheet.Range("A1").CurrentRegion
.AutoFilter field:=1, Criteria1:=Arr, Operator:=xlFilterValues
.Offset(1, 0).EntireRow.Delete
.AutoFilter

End With