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!
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!