Programmatically removing data filters

[PatK]

I have a spreadsheet that, using VBA macros, I add rows to the end of the
table, based upon data in a database. HOwever, I have found that, if, for
whatever reason, I have a filter set on one or more columns at the time I
attempt the update, I get an error in the VBA code. My work around is to
simply ensure I have no filters set, before I run the code, but it would be
great if I could programmatically "unset" all filters on all columns, before
I do the row add activity.

Is there a quick/dirty way to do that?

thanks!

Patk

 

[Mike H]

Try

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

Mike

 

[Dave Peterson]

With worksheets("Sheet9999")
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
End With

 

[Mike H]

apologies that will remove the filter and I think you want to reset it to
all. Try this

With ActiveSheet
If .AutoFilterMode Then
.ShowAllData
End If
End With

Mike

 

[Mike H]

I'll get it right in a minute

With ActiveSheet
On Error Resume Next
If .AutoFilterMode = True Then
.ShowAllData
End If
End With

Mike

 

[PatK]

That sure seemed logical enough, but it is not working. Here is the code and
note that, while I have the sheet I wanted, active, and it clearly has a
filters set, and one of the columns filtered, the debug is not triggering (so
assume the showalldata wont' work, either). I also tried naming the sheet as
Dave noted..same result. What am I missing? I do have a popup menu open
that they use to "trigger" the event, but that should not be considered a
worksheet. I even tried executing it simlply from VB, to bypass the menu,
but no joy. Ideas? Thanks for all your help!

Patk

Sub ClearFilters()
With ActiveSheet
On Error Resume Next
If .AutoFilterMode = True Then
Debug.Print "autofilter on detected"
.ShowAllData
End If
End With

End Sub

 

[Mike H]

It works for me.

Do you have macros enabled?

Mike

 

[Dave Peterson]

How are you running the macro?
Tools|Macro|macros|run????

And you could have changed the name in the code instead of renaming the
worksheet.

 

[PatK]

I actually have a menu button on the spreadsheet. Ie, you press it and it
pops up a menu, that executes various VBA subroutines. The other subroutines
and menu are working just fine. Just trying to fix the nagging problem with
the filter. I created a little subroutine called ClearFilters which you can
see below, and that is the first step in another subroutine, ie, I clear
filters (or hope to), before I do the other work on the spreadsheet. HEre is
that code, if that helps:

Sub main(dummy)
ClearFilters
SheetCopy "Sunset-Plan-Last", "Sunset-Plan"
If SheetExists("HPSC") Then DeleteAllRows ("HPSC")
hpscAssetData ("HPSC")
If SheetExists("Sunset-Detail") Then DeleteAllRows ("Sunset-Detail")
hpscSunsetData ("Sunset-Detail")
CompareSheets "Sunset-Plan", "Sunset-Plan-Last", "E"
CompareSheets "Sunset-Plan", "Sunset-Plan-Last", "F"
CompareSheets "Sunset-Plan", "Sunset-Plan-Last", "K"
CompareSheets "Sunset-Plan", "Sunset-Plan-Last", "B"
HideWB ("Sunset-Plan-Last")
Worksheets("Sunset-Plan").Activate



End Sub

 

[Dave Peterson]

And if you step through the code, does it work ok?

If you used one of Joel's suggestions with the "on error" line, you may want to
remove it to see if you get an error returned.

(any chance the worksheet is protected?)

 

[PatK]

I changed the code to look like this (commented out the on error) and added
the debugs. I get the "Start "messages, and the "not true" message. The
active sheet clearly has a filter set on the column, so, no joy. Is there a
way to debug the value of what excel thinks the active sheet is at that
instant? I certainly don't see the sheet changing, and the Clearfilters sub
is the first step in the main subroutine, so the sheet cannot have changed
(well...as far as I can see). thought? (and thanks!)

Patk

Sub ClearFilters()
Debug.Print "Start"
With ActiveSheet
'On Error Resume Next
If .AutoFilterMode = True Then
Debug.Print "autofilter on detected"
.ShowAllData
Else
Debug.Print "not true"
End If
End With
end sub

 

[PatK]

Ok...figured it out! Looked around in some of the other threads where
autofilters will discussed, and saw a similar test being done in a code
snippet. The difference as using .autofiltermode vs .filtermode. When I
dropped the auto, this worked fine.

Hurray! Thanks for helping me get pointed in the right direction!

Patk

Sub ClearFilters()
With Worksheets("Sunset-Plan")
On Error Resume Next
If .FilterMode = True Then
Debug.Print "autofilter on detected"
.ShowAllData
Else
Debug.Print "not true"
End If
End With

End Sub

 

[Dave Peterson]

And you're sure you're looking at the activesheet?

Can you explain why you think the sheet has a filter set on the column?

Any chance you've just hidden some rows by adjusting the rowheight?

I changed the code to look like this (commented out the on error) and added
the debugs. I get the "Start "messages, and the "not true" message. The
active sheet clearly has a filter set on the column, so, no joy. Is there a
way to debug the value of what excel thinks the active sheet is at that
instant? I certainly don't see the sheet changing, and the Clearfilters sub
is the first step in the main subroutine, so the sheet cannot have changed
(well...as far as I can see). thought? (and thanks!)

Patk

Sub ClearFilters()
Debug.Print "Start"
With ActiveSheet
'On Error Resume Next
If .AutoFilterMode = True Then
Debug.Print "autofilter on detected"
.ShowAllData
Else
Debug.Print "not true"
End If
End With
end sub