VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
I have used some VBA code to set the Autofilter on in Excel. The AutoFilter works fine the first time the code is run but if the code is run again even though the filter buttons appear they will not 'press' (and all the filter buttons show black down-arrows on grey backgrounds with no filtering actually applied) and the filter cannot be used. :x
Data-->Filter-->AutoFilter turns it off OK but I can not get VBA to ensure the filter is off before applying it. :rolleyes: please.
Miles
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Andrew, That was fast thanks. I have tried the
Code:
ActiveSheet.Cells.AutoFilter
but alas it toggles the state of the filter rather than forcing it off regardless of its on/off condition.
Miles
 
Upvote 0
I thought you said it was on. Anyway:

Code:
If ActiveSheet.AutoFilterMode Then
    ActiveSheet.Cells.AutoFilter
End If
 
Upvote 0
Andrew, Thanks once again. The AutoFilterMode was just what I needed but didn't spot it in the help index! All now works fine if I use
Code:
Selection.AutoFilter
however the following code displays the down arrows but they are locked and unusable and the filtering is NOT applied.
Code:
Range("A1:L1").AutoFilter
Perhaps Range("A1:L1") is not a valid range objetc in this case.
Miles
 
Upvote 0
Doh! I had initially recorded this bit of code to get an idea of the syntax but did not notice that I had selected the cells A1:L1 only when I really intended to select the columns A:L. Thanks for spotting it.
Miles
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top