Show All Data

[Guest]

I have some quite complex filters and am trying to make the spreadsheet more
user friendly.

I have included a button to restore the Show All Data using the VBA:

ActiveSheet.ShowAllData

This works, but only if there has been a filter applied. If the user clicks
the button again (without a filter being present) I get the following error
message - "Run Time error 1004 - ShowAllData method of Worksheet class
failed".

How can I stop this error message, or better still advise the user (MSGBOX?)
that all the data is currently being shown?

Thank you for your help.

 

[Gary Keramidas]

maybe something like this

If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.ShowAllData
End If

 

[Guest]

if activesheet.filtermode then
activesheet.showAlldata
End if

 

[Gary Keramidas]

sorry, should have posted this

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If

 

[Snake Plissken]

Sub Makro2()

If ActiveSheet.FilterMode = False Then
MsgBox "No filter !"
ElseIf ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
MsgBox "unknown case"
End If


End Sub

 

[Guest]

Thanks everyone - works perfectly

 

[bobvba]

Is there any way to query if any sort conditions are active on the sheet in a similar fashion to the code below? I've got a button to clear all the filters/sorts that wouldn't work if showalldata=true, so I used your little gem below and it works great. Only problem is that it will only work if a filter is present, not if just a sort has been applied. thanks

Sub Makro2()

If ActiveSheet.FilterMode = False Then
MsgBox "No filter !"
ElseIf ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
MsgBox "unknown case"
End If


End Sub