Autofilter Method of range class failed

[Tolus]

I try to run the following code, where I find the proper column in a sheet to format it.
Then I use Autofilter to set a filter criteria on the same sheet (but different value).

the following error appears: Autofilter Method of range class failed

If I use the very same code, but format the two colomns in the beginning using fixed range

    'Range("C:C").NumberFormat = "General"
    'Range("E:E").NumberFormat = "General"

it works fine.
Please help

    Dim Row as Variant
    Dim FilterRow as Variant

    Row = Rows("1:1").Find(What:="Erstellt", LookAt:=xlWhole).Column
    Range(Cells(1, Row), Cells(100000, Row)).NumberFormat = "General"

    Row = Rows("1:1").Find(What:="bestätigt", LookAt:=xlWhole).Column
    Range(Cells(1, Row), Cells(100000, Row)).NumberFormat = "General"
   

    If WrkShtRSTand.AutoFilterMode Then
    WrkShtRSTand.ShowAllData
    Else
    WrkShtRSTand.Range("A1").AutoFilter
    End If
     
    FilterRow = Rows("1:1").Find(What:="Einkäufergruppe", LookAt:=xlWhole).Column
    WrkShtRSTand.UsedRange.AutoFilter Field:=FilterRow, Criteria1:=Array("AZ04", "AZ05", "AZ07", "AZ08"), Operator:=xlFilterValues

 

[Yongle]

try this

    Row = Rows("1:1").Find(What:="Erstellt", LookAt:=xlWhole).Column
    Range(Cells(1, Row), Cells(100000, Row)).NumberFormat = "General"

    Row = Rows("1:1").Find(What:="bestätigt", LookAt:=xlWhole).Column
    Range(Cells(1, Row), Cells(100000, Row)).NumberFormat = "General"
    
    With WrkShtRSTand
        On Error Resume Next
         .ShowAllData
        On Error GoTo 0
        FilterRow = .Rows("1:1").Find(What:="Einkäufergruppe", LookAt:=xlWhole).Column
        .UsedRange.AutoFilter Field:=FilterRow, Criteria1:=Array("AZ04", "AZ05", "AZ07", "AZ08"), Operator:=xlFilterValues
    End With

 

[Tolus]

Hi Yongle

Thank you for your reply.
I still got the same error message.
Its on the same line of the code:
(
.UsedRange.AutoFilter Field:=FilterRow, Criteria1:=Array("AZ04", "AZ05", "AZ07", "AZ08"), Operator:=xlFilterValues
)
Do you want to see more of the code?
Would that help?

KR
Tolus

 

[Yongle]

It works for me. Something is different in your data

Insert this above that line

MsgBox FilterRow

Message box should return a number > 0

 

[RoryA]

Is there an existing autofilter on the sheet? If so, what range is it filtering? Also, where does the data start? If it's not in column A, then the filter column number you are passing might be too large.

 

[Tolus]

Yes it does - its 4.
The code works if I use:

'Range("C:C").NumberFormat = "General"
'Range("E:E").NumberFormat = "General"

instead of

Row = Rows("1:1").Find(What:="Erstellt", LookAt:=xlWhole).Column
Range(Cells(1, Row), Cells(100000, Row)).NumberFormat = "General"

Row = Rows("1:1").Find(What:="bestätigt", LookAt:=xlWhole).Column
Range(Cells(1, Row), Cells(100000, Row)).NumberFormat = "General"

KR
Tolus

 

[Tolus]

Hi RoryA

There is no autofilter active at the moment.
The filter range is A1:U1
The first data line is A2:U2 and goes until 1870

What do you mean with the filter column number might be too large?

KR
Tolus

 

[RoryA]

Your code applies a filter to the Usedrange and you are getting the filter column by using the column number of the found cell. So if column A were empty, you'd be filtering from column 2 (B) but the field number should still start at 1 for column B, not 2.

Any chance you can put the workbook somewhere for us to look at (eg Onedrive or Dropbox)?

 

[Tolus]

Oh I see - it does start on column a.
The workbook with the code is: OPEK KPI_EN V2.xlsm the one it pulls data from is: Bestellrückstand.xlsx
here is the link to google drive: Google Drive

Thank you for your help.
KR
Toby

 

[Tolus]

There is some weird German comments in the code - just ignore it