Autofilter Issue

[paddysheeran]

Hi All,

I need help with an Autofilter issue I cannot tie down. I want to check the state of the autofilter to see whether it is on or not and applied. If it applied do nothing. If it isn't apply it:


If Worksheets("Sheet1").FilterMode = True Then
Else
Selection.Autofilter
End If


Whenever I check the state of Worksheets("Sheet1").FilterMode in the Immediate window it is always false regardless of whether the autofilter is applied or not

The first time through the code if the autofilter is already applied it will be removed. The second time through the code if the autofilter is not applied it is added!!

I cant understand what the problem is??

any help greatly appreciated

thanks,

Paddy

[GarysStudent]

There is a difference between FilterMode and AutoFilterMode. Once you have switched on an AutoFilter and the drop-downs are visible, AutoFilterMode will become True. If then actually use the drop-downs to activate the filter, FilterMode will also become True!

[paddysheeran]

Hi,

I have changed this to the following and still have the same problem:


If Worksheets("Sheet1").AutoFilterMode = True Then
Else
Selection.AutoFilter
End If

If the autofilter is on it is turned off. If the autofilter is off it is turned on. I just need it to recognise if the autofilter is on the keep it on. If off then turn it on.

The Else portion of the code is fired regardless of whether then autofilter is on or not...

Investigating this further I have tried the code on a normal header and it works fine. The problem is when using this on the header of a Table. The Table name is "Avail_Incidents". Is there a way round this for Table autofilters?

thanks,

paddy.

[p45cal]

1. Where is the code? In a sheet's code module, or a standard code-module.. or somewhere else?
2. Are you sure that the sheet which is active at the time you run the code is actually Sheet1 ?!

[paddysheeran]

1. Where is the code? In a sheet's code module, or a standard code-module.. or somewhere else?
2. Are you sure that the sheet which is active at the time you run the code is actually Sheet1 ?!

Hi, the full code is:


Sheets("Sheet1").Select

If Worksheets("Sheet1").AutoFilterMode = True Then
Else
Selection.AutoFilter
End If

[paddysheeran]

in a standard code-module. As shown before the autofilter needs to be applied to the header of the table on sheet1

[p45cal]

…and point 2 in msg#4 above?

[Mr.Rhett]

Try the above code...

[Aflatoon]

Are you using a Table rather than a regular range?