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

Try

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

Mike

"PatK" wrote:

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

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" wrote:

Try

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

Mike

"PatK" wrote:

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

I'll get it right in a minute

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

Mike

"Mike H" wrote:

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" wrote:

Try

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

Mike

"PatK" wrote:

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

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" wrote:

I'll get it right in a minute

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

Mike

"Mike H" wrote:

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" wrote:

Try

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

Mike

"PatK" wrote:

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

It works for me.

Do you have macros enabled?

Mike

"PatK" wrote:

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" wrote:

I'll get it right in a minute

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

Mike

"Mike H" wrote:

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" wrote:

Try

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

Mike

"PatK" wrote:

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

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 wrote:

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" wrote:

I'll get it right in a minute

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

Mike

"Mike H" wrote:

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" wrote:

Try

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

Mike

"PatK" wrote:

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

--

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



PatK wrote:

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

--

Dave Peterson