VBA Autofilter

terpsbmore

New Member
Joined
Jan 20, 2017
Messages
4
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!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
Isn't the range being filtered only 1 Field Wide...?
Code:
.AutoFilter Field:=[COLOR=#ff0000]1[/COLOR], Criteria1:="<>*BAD LEAD*"
 

terpsbmore

New Member
Joined
Jan 20, 2017
Messages
4
Isn't the range being filtered only 1 Field Wide...?
Code:
.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

New Member
Joined
Jan 20, 2017
Messages
4
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
Changed it up and works in Excel 2016.
How many rows is the source data?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,222
Messages
5,594,899
Members
413,950
Latest member
solve22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top