VBA, Autofilter method of Range Class Failed,-CodePudding

I am getting the error Autofilter method range of class failed.

I am thinking it is because there is a column space in my headers but not entirely sure as to how to get around this. Getting the error on this line:

ws.Range("$A:$K").AutoFilter field:=10, Criteria1:="#N/A"

I have my range till K, but when it gets to a blank column, ie "I" , the autofilter is only applied till there.

Also if i manually apply a filter to the whole first row the macro works.

Why is this?

Ive tried using A1:K1 and that doesnt work. When I debug and manually go into apply the filter to the remaining columns the code works fine.

CodePudding user response：

Reference a Discontinuous Range to Apply AutoFilter

All four solutions could (should) work. Your feedback is most welcome.
A rule of thumb: Don't let Excel decide (if you don't have to)... in this case,
to set the range for you.

Sub FilterData()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    If ws.FilterMode Then ws.ShowAllData ' clears any filters
    
    Dim rg As Range
    ' If your data is the only thing in the worksheet:
    Set rg = ws.UsedRange
'    ' If there are more columns:
'    Set rg = Intersect(ws.UsedRange, ws.Columns("A:K"))
'    ' or:
'    Dim rg As Range: Set rg = ws.Columns("A:K")
'    Dim lCell As Range
'    Set lCell = rg.Find("*", , xlFormulas, , xlByRows, xlPrevious)
'    Set rg = rg.Resize(lCell.Row)
'    ' If additionally there are other data below your data:
'    Set rg = ws.Range("A1").CurrentRegion.Resize(, 11) ' K = 11
    
    rg.AutoFilter Field:=10, Criteria1:="#N/A"

End Sub

CodePudding user response：

Is your worksheet protected? If so, i recommend removing the worksheet protection and adding it again after your filter code.