VBA Autofilter

[terpsbmore]

Hello All,

I am pretty new to Excel VBA. I haven't used vba in years and I am now relearning it. I am stuck on a macro that formats/filters a list of data. I did not write the code and I am having a difficult time debugging it. Hopefully someone here can help.

I am receiving a runtime error 1004 and after reviewing multiple posts here I believe it has to do with the defined range. Specifically, the error is "Autofilter method of Range class failed".

Here is the code:

Sub FilterBadLeads()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long


Set ws = ActiveWorkbook.Sheets("Data")


lastRow = ws.Range("L" & ws.Rows.Count).End(xlUp).Row


Set rng = ws.Range("L1:L" & lastRow)


' filter and delete all but header row
With rng
.AutoFilter Field:=12, Criteria1:="<>*BAD LEAD*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With


'turn off the filters
ws.AutoFilterMode = False

End Sub

Any help is greatly appreciated!

 

[SpillerBD]

Isn't the range being filtered only 1 Field Wide...?

.AutoFilter Field:=[COLOR=#ff0000]1[/COLOR], Criteria1:="<>*BAD LEAD*"

 

[terpsbmore]

Isn't the range being filtered only 1 Field Wide...?

.AutoFilter Field:=[COLOR=#ff0000]1[/COLOR], Criteria1:="<>*BAD LEAD*"

The range being filtered is 14 columns wide. My understanding is the "Field" property is the column number which you want to sort by. The number being determined by offset from the leftmost column. Is this not correct?

 

[terpsbmore]

I changed Field to "Field:=1" and excel froze...any other advice?

 

[terpsbmore]

The range being filtered is 14 columns wide. My understanding is the "Field" property is the column number which you want to sort by. The number being determined by offset from the leftmost column. Is this not correct?

I appreciate your help. Still trying to find a solution.

 

[SpillerBD]

Changed it up and works in Excel 2016.
How many rows is the source data?