I want excel to unfilter cells before user saves

[andresmcc]

I have a spreadsheet that is updated by several users. Some times people save chnges to it and forget to unfilter or unhide cells. This is anoying to other users. Is there a function, macros, or VBA Code that can unfilter and unhide before saving, or at least remind the user to do so before saving his changes?

Thanks

 

[Juan Pablo González]

This lines should help you achieve that

Sheet1.AutoFilterMode=False
Cells.EntireRow.Hidden=False
Cells.EntireColumn.Hidden=False

 

[andresmcc]

Gracias Juan Pablo

I wasn't very clear though. I want Excel to do this everytime a user clicks on "Save". That is the part I don't know how to do. How would I program Excel to perform this on each save?

 

[GeorgeB]

Try this.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Integer
Dim f As Filter
Dim w As Worksheet
Set w = Worksheets("YOUR SHEET NAME")
Worksheets("YOUR SHEET NAME").Activate
c = 0
Application.ScreenUpdating = False
For Each f In w.AutoFilter.Filters
c = c + 1
Selection.AutoFilter Field:=c
Next

End Sub

 

[Damon Ostrander]

Hi Andress,

Here is some code that you can put in the ThisWorkbook event module to expand all the filters on the active worksheet before saving. If you want to target a specific worksheet other than the active worksheet just replace ActiveSheet with Worksheets("Your worksheet name") in the code below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Expand (turn off) all filters on the active worksheet
Dim Fltr As Filter
Dim iFiltr As Integer
With ActiveSheet.AutoFilter
For iFiltr = 1 To .Filters.Count
If .Filters(iFiltr).On Then
.Range.AutoFilter field:=iFiltr
End If
Next iFiltr
End With
End Sub

To install this code simply right-click on the Excel icon at the left end of the Excel Worksheet Menu Bar, select View Code, and paste the above code into the VBE code pane.