Excel VBA autofilter error

Question

I am trying to make my work easier. I have an worksheet with a pivottable which get its data from a ms sql database (over 100000 records, if that counts). I need to autofilter this data. So I google for it.

I have found this vba code:

Private Sub Worksheet_Calculate()

Range("A11").CurrentRegion.AutoFilter field:=6, Criteria1:=Range("d5").Value

End Sub

I concluded that I can apply it to my case.

The range which I need to filter starts from A11, the filter should be applied to field no.6 by the value found in cell D5.

At first it does the filtering, but then I get this error:

Method 'Autofilter' of object 'Range' failed

I don't know what is wrong, excel gets stuck and nothing works

Maybe someone could give me a hint or something.

Thank you.

Record a macro whilst performing the task and see what it gives you. Also, check how you have your data set up e.g. table, pivottable...... — QHarr
What are the real limits of the table ??........ A11 through what ?? — Gary's Student
I guess it has something to do with the fact that the datas are setup as a table which gets its data from an external source. I have pasted special the datas in another worksheet and there is no error in this case. I guess I could use this worksheet, with the data I have pasted in. — byte00

Cyril Cyril · Accepted Answer · 2017-10-13T13:51:57

I would recommend trying a fully-defined range for the autofilter, such as:

Sub fdsa()
    Dim LR as Long 'LR is last row
    LR = Cells( Rows.Count, "D").End(xlUp).Row 'Chose Col D to sort as it's the filter range
    Range("A11:O" & LR).AutoFilter field:=6, Criteria1:=Range("D5").Value 'Arbitrarily chose O as the stop column in the range... adjust as necessary
End Sub

Excel VBA autofilter error

1 Answers