On workbook close undo advanced filter

[Xx7]

I have filtered data in Sheet1. When I close the workbook I would like a macro to automatically unfilter the data. Therefore, when I open it next time, all the data is unfiltered on Sheet1. This only has to occur on Sheet1. I would prefer that no adjustments be made to the other sheets.

 

[Gerald Higgins]

I sometimes do something similar, but the way I do it is to use an auto execute macro which runs whenever I OPEN the file, to remove any filtering that may be in place.

Is that an option for you ? If yes, I'll post some code.

 

[ZVI]

Two variants of code, the first one is that Gerald has mentioned:

' Put code into ThisWorkbook module

' Unhide rows hidden by filter (at opening of workbook)
' (Suggestion of Gerald Higgins)
Private Sub Workbook_Open()
  With Sheets("Sheet1")
    If .FilterMode Then .ShowAllData
  End If
End Sub

' Unhide rows hidden by filter (at saving of workbook)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  With Sheets("Sheet1")
    If .FilterMode Then .ShowAllData
  End If
End Sub

 

[ZVI]

Please replace End If by End With in the code above.
The fixed code:

' Put code into Thisworkbook module

' Unhide rows hidden by (advanced) filter at opening of workbook
' (Suggestion of Gerald Higgins)
Private Sub Workbook_Open()
  With Sheets("Sheet1")
    If .FilterMode Then .ShowAllData
  End With
End Sub

' Unhide rows hidden by (advanced) filter at saving of workbook
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  With Sheets("Sheet1")
    If .FilterMode Then .ShowAllData
  End With
End Sub

 

[Xx7]

I sometimes do something similar, but the way I do it is to use an auto execute macro which runs whenever I OPEN the file, to remove any filtering that may be in place.

Is that an option for you ? If yes, I'll post some code.

That could work. I'm running a macro to insert hyperlinks into the worksheet on open too. However I need to unfilter the sheet first or else my hyperlinks don't update correctly. I'm assuming I can do both of these on open?? Just unfilter them first... or will these conflict?

 

[Xx7]

ZVI,

I'm getting a "run-time error '9': Subscript out of range", when I run your revised code. Any advice?

 

[ZVI]

ZVI,

I'm getting a "run-time error '9': Subscript out of range", when I run your revised code. Any advice?

It was assumed that Sheets("Sheet1") is present, if the sheet name is different then replace "Sheet1" in the code by the actual sheet name

 

[Xx7]

Ahaaaa... works perfect now!