Auto filter 1004 Error

[BrizzleC]

I am trying to filter and sort the Top 10 values in my spreadsheet but it keeps throwing up the '1004 Autofilter method of Range class failed' error, with the third line being highlighted.

My code is:

Range("B9:CS50").Select
Selection.Autofilter
Selection.Autofilter Field:=53, Criteria1="10", Operator:=xlTop10Items
Range("B10:CS50").Sort Key1:=Range("AW10"), Order:=xlDescending, Header _
   :=xlGuess, OrderCustom:=1, MatchCase:=False, Oreientation:=xlTopToBottom _
   , DataOption1:=xlSortNormal

The column being filtered and sorted contains £values but some are #N/A - will this be what is affecting the code?
If so, how do I get around this?

Many Thanks

 

[mole999]

is
Selection.Autofilter turning on the visible filter buttons

 

[BrizzleC]

Yes it does.

 

[mole999]

what does doing it twice in a row do to it

 

[BrizzleC]

ADVERTISEMENT

It stops on the second one, so I don't know.
I removed the first bit of code, so took out the repetition, and it still stops at the same line of code.
Have also tried to manually do a top 10 filter and it won't filter anything, although it will do a greater than and less than filter etc.

 

[mole999]

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

will turn off the filter if it is on

then when you issue the autofilter it isn't toggled

my system highlights an error with

Selection.Autofilter Field:=53, Criteria1="10", Operator:=xlTop10Items

maybe record it again and see if there was something missed

 

[BrizzleC]

I already have that line of code at the start, which removes the filtering before I reapply.

As I've said, I am unable to manually "Top 10" the list so cannot record the MACRO exactly so I did it on another column which worked and just changed the field value to the correct one.

Just thinking that the #N/A cells are stopping the filter from working so I have used another column "3" to filter those elements out but still the Top10 filter on "53" won't work, vba or manually. Help?!