CODEWITHSUNDEEP
excelvba
Jonnyboi
Jonnyboi

Reputation: 557

VBA, Autofilter method of Range Class Failed,

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.

Upvotes: 1

Views: 843

Answers (1)

VBasic2008
VBasic2008

Reputation: 54787

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.AutoFilterMode Then ws.AutoFilterMode = False 
    
    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

Upvotes: 1

Related Questions