can't remove autofilter

[RB Smissaert]

Using Excel 2000 to 2003, but developing in 2003.
I want to set an autofilter in the sheet in VBA. I don't want to set any
filter, but just make the filter
arrows show. The problem is if there is a filter in place already, hiding
some rows.
From postings to this group I thought that ActiveSheet.AutoFilterMode =
False should clear any filter, but it
doesn't. So when there is a filter in place say "female" and I do:

ActiveSheet.AutoFilterMode = False
'also tried Sleep(500) here
Selection.AutoFilter

I expected to see all the rows, but this is not the case.

I also tried:

If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter 'to toggle off
'also tried Sleep(500) here
Selection.AutoFilter 'to toggle on
Else
Selection.AutoFilter
End If

But that doesn't work either.

How do I solve this?


RBS

 

[Frank Kabel]

Hi
try
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilterMode = False 'to toggle off
Else
Selection.AutoFilter 'add your fields + criteria here
End If

 

[Ken Wright]

Using Excel 2000 to 2003, but developing in 2003

Firstly, I wouldn't - You should really develop in the lowest version that your
spreadsheet will be used in, as you have far less worries about backwards
compatibility.

Secondly, you can just apply the filter again but without specifying a criteria,
eg:-

Range.AutoFilter Field:=1, Criteria1:="4" will put it on

Range.AutoFilter will take it off

 

[Tom Ogilvy]

if Activesheet.AutofilterMode then
if Activesheet.filterMode then
Activesheet.ShowAll
end if
Activesheet.Rows.Hidden = False
else
Activesheet.rows.Hidden = False
ActiveSheet.Range("A1").CurrentRegion.Autofilter
End if

however,
Activesheet.AutofilterMode = False
has always worked for me.


Above is untested.

 

[RB Smissaert]

Frank,

Not sure why this would work.
Tried it anyhow and it indeed doesn't work.
If I run If ActiveSheet.AutoFilterMode = False separately in a different Sub
it will turn the filter off fine.
So why doesn't this work:

ActiveSheet.AutoFilterMode = False
Cells(2, 2).AutoFilter

This is puzzling.


RBS

 

[RB Smissaert]

Tom,

Thanks, your code works nicely.

RBS

if Activesheet.AutofilterMode then
if Activesheet.filterMode then
Activesheet.ShowAll
end if
Activesheet.Rows.Hidden = False
else
Activesheet.rows.Hidden = False
ActiveSheet.Range("A1").CurrentRegion.Autofilter
End if

however,
Activesheet.AutofilterMode = False
has always worked for me.


Above is untested.