VBA Autofilter macro works on some sheets not on others

[teatimecrumpet]

Hi,

I've got a macro that will filter and then delete the resulting rows. The macro works fine on all sheets except for one sheet.

The sheet is no different than the other ones in header or information. It does have about 80k rows of data before the below macro is run the other sheets have ranged in number of rows from 10k to 40k. It looks like the macro runs (when screen updating is off) but nothing is deleted as it should be.

I have one workbook where I filter a copy of the first sheet and filter on that before copying and pasting as values to other worksheets I've created. The last worksheet I create is no different than the others except it is all the values from the "original" so no filters are applied before the copy and paste. I've also tried to duplicate the original sheet.

I then try to run the code below but no dice.

Any thoughts?


Thanks,

Sub SC__TEST()
Dim rng As Range
Dim calcmode As Long
Dim myArr As Variant

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'Fill in the values that you want to delete
myArr = Array("Yellow", "Blue", _
"Green)

For I = LBound(myArr) To UBound(myArr)
With ActiveSheet
'Firstly, remove the AutoFilter
.AutoFilterMode = False
'Apply the filter
.Range("L1:L" & .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

End Sub

 

[teatimecrumpet]

Hi All,

I've been able to get the below code to work only if I run another filter on it first copying pasting and then letting the below code run on the new sheet.

But why it wouldn't work on the original sheet has got me stumped.

Thanks.