Auto Filter Mode - Show all

[prasath]

In one of my macro, i am struggling debug with autofilter option. If the sheets are filtered, the macro is not working.

For this,

When i run the macro, it should check "If any data is filtered by Autofilter mode, if so, it should free it (show all)"

Note : I prefer this auto filter check should be done in the all the worksheets of active workbook.

Or, if there has been a better way, guide me..

Thanks in advance

Prasath RH

 

[Tom Urtis]

The line

ActiveSheet.AutoFilterMode = False

should take care of that, if you place it in your macro at the point where you don't want errors to occur. No error bypasses or error handlers are needed.

 

[njimack]

ActiveSheet.ShowAllData

 

[Tom Urtis]

If you are going to use ActiveSheet.ShowAllData, enclose it in an error bypass in case the sheet is not in Filter mode.

On Error Resume Next
ActiveSheet.ShowAllData
Err.Clear

 

[prasath]

ActiveSheet.ShowAllData

Thanks for your reply..

However, ShowAllData is getting bugged when there is no filter is used..

 

[Tom Urtis]

As I pointed out that possibility, please see my prior message in that situation.

 

[prasath]

The line

ActiveSheet.AutoFilterMode = False

should take care of that, if you place it in your macro at the point where you don't want errors to occur. No error bypasses or error handlers are needed.

Thanks for your reply. .

I would like to add this check point for all my worksheets in the active workbook.. How to do that..

 

[Tom Urtis]

Your last question is not clear. If this is for an existing macro and you are looping through worksheets, this example might be what you are asking about:

Dim ws as Worksheet
For each ws in Worksheets
On Error Resume Next
ws.ShowAllData
Err.Clear 
Next ws

 

[uson]

Tom,

How could I imbed this in a worksheet to make sure the filters were cleared upon exiting or opening the worksheet? We have worksheet shared by a group. Some members complain of missing data not realizing that filtering is on from the last team member. Ideas?

 

[Tom Urtis]

What you could do is, each time the workbook opens, you can undo any filters on any sheets with this code in the workbook module:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
ws.ShowAllData
Err.Clear
Next ws
Application.ScreenUpdating = True
End Sub

Remember, that's the *Workbook* module. To locate it:
- from your worksheet press Alt+F11
- press Ctrl+R
- Find the workbook name in the Project window (vertical pane on the left), and expand its yellow folder named Microsoft Excel Objects.
- Find the object named ThisWorkbook, right click on it, select View Code, and paste the above code into the large white pane that is the workbook module.

Press Alt+Q to return to the worksheets.

Press Ctrl+S to save the workbook.

Now whenever the workbook closes and is reopened, no sheets will be filtered.