CODEWITHSUNDEEP
vbaexcel
RShome
RShome

Reputation: 537

Run-time error 1004 with Range.Autofilter in Excel VBA 2016

My code is

With ActiveSheet
   .AutoFilterMode = False
   .Range("23:23").AutoFilter
End With

This works fine in Excel 2010, but with Excel 2016 I get:-

Run-time error '1004' AutoFilter method of Range class failed

Also, I can manually click the filter icon in the Ribbon (Under Data > Filter) but cannot do this with VBA code

Any ideas much appreciated.

Upvotes: 0

Views: 2776

Answers (2)

Rakesh Rudran
Rakesh Rudran

Reputation: 1

Remove the Existing filer and Run it again

Upvotes: 0

Vityata
Vityata

Reputation: 43585

The AutoFilter gives 1004 error, when you are trying to filter by an empty row. Try to put something on row 23 and to filter it again like this:

Public Sub TestMe()    
    With ActiveSheet
       .AutoFilterMode = False
       .Range("23:23").Cells(1) = 1
       .Range("23:23").Cells(2) = 2
       .Range("23:23").AutoFilter
    End With

End Sub

If it works, then you simply do not have values in row 23, thus it cannot apply an autofilter.

In general, the AutoFilter in Excel has some strange behaviour. E.g., if you open a new Excel file and you run the following code:

Public Sub TestMe()

    With ActiveSheet
       .AutoFilterMode = False
       '.Range("23:23").Cells(1) = 1
       '.Range("23:23").Cells(2) = 2
       .Range("23:23").AutoFilter
    End With    
End Sub

It will give you the 1004 error. Let's call this time momentum FirstTime.

Then, if you uncomment the two ranges and run it, the AutoFilter would appear.

Now the strange part - delete all cells from the sheet, comment back the two ranges and it really looks like the way it was, at the FirstTime. But if you run the code it will put an AutoFilter on the empty 23rd row without a problem.

Upvotes: 1

Related Questions