Sleeper: Reset of Auto-Filter

[golf4]

Hi, everyone -

I'm having a problem with one of my projects. The project involves a spreadsheet that tracks vacancy loss for all of our housing units. I have the Auto-Filter turned on in all of the columns so that Staff can filter by employee, unit location, etc. The spreadsheet is on a shared drive where a number of employees use the data. The problem comes in when they filter one of the columns, but fail to "unfilter" it before the close or save. When this happens, and the next employee looks at the data, if they dont see that one of the columns is filtered, the data will look skewed.

I've tried to use the code below, to unfilter all of the columns but not turn off the Auto-Filter before closing and/or saving and it works great at home. When I try to use it at the office, doesn't seem to work.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        With wks
            If .AutoFilterMode Then
                If .FilterMode Then
                    .ShowAllData
                End If
            End If
        End With
    Next wks
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        With wks
            If .AutoFilterMode Then
                If .FilterMode Then
                    .ShowAllData
                End If
            End If
        End With
    Next wks
End Sub

Any ideas/suggestions would be great. Thanks

Frank

[Ken Puls]

Hey Frank,

Don't know if it's the right way, but I always just set autofiltermode to false, then added the autofilter again.

[geekgirlau]

Hey, great minds Ken!

[Ken Puls]

Hey, great minds Ken!

LOL! Cool stuff! I always wonder if I don't just make things harder than they need to be sometimes!

(Now watch, someone's going to come along and show us a one liner to do it! )

[Zack Barresse]

Frank! How ya been buddy!! Long time no see. Hope all has been well with you. (I'm still showing off those medals!!)

And Ken's idea would be about the same amount of code also .. But out of curiosity, are there any version differences here?

[Ken Puls]

And Ken's idea would be about the same amount of code also .. But out of curiosity, are there any version differences here?

Memory isn't always so good about these things, but I'm pretty sure I've been coding that way since I learned to code in Excel 97...

[Zack Barresse]

Memory isn't always so good about these things, but I'm pretty sure I've been coding that way since I learned to code in Excel 97...

I meant Frank, if there were any differences in the versions of Excel he tested it with..

.. it works great at home. When I try to use it at the office, doesn't seem to work.

Trying to isolate the variable..

[Ken Puls]

Oh! Gotcha!

[golf4]

Hi, guys -

Sorry it took me so long to bet back to you, but work has been a real "challenge" to say the least. Just rolled in about 10 mins ago. AHHHHH - the life of a dedicated public servant............ right, Zack????!!!!

Ken - thanks for your suggestion. Is there a way to modify my existing code to set the autofiltermode to false then back on? I'm wondering, now, whether Zack's idea of a difference in versions may have something to do with it. The saved copy of the spreadsheet, at home, is using Excel 2000. When I enter the same exact code into the same spreadsheet at the office, using Excel 2002, doesn't seem to work. UUUUGGGGG!!!!

Zack - how's it going, bud???? It has been far too long. Been swamped at work, and gettin home too late to do anything on the net but check email. Great to hear the medals are holding up for ya. It must be the little microphones built into them to hold up in the Eastern Oregon weather.....

How are things going in the life saving biz? Hearing you guys are staying real busy over there. Keep your head down and stay safe, will ya? Give my best to the family too......

Frank

[Ken Puls]

Hi Frank,

Sorry for the delay on this. Strange on the version thing. Most code works going forward, but sometimes has issues going backwards... I don't see anything strange in there myself...

At any rate, to reset the filter, I'd go with something like this:

With wks
If .AutoFilterMode Then
   .AutoFilterMode = False
   .Rows("1:1").AutoFilter
End If
End With

Thing is, you need to have a consistent row/area to filter if you're going to use it in a loop.

Hope it helps,

[golf4]

Hi, Ken -
Thanks so much for the help on this one. I modified my code with your suggestions, and it cleared the filtering. The thing is it also removed the Auto-Filter from the row. Is there a method to clear the filtering, say, in Row 4 but leave the Auto-Filter on?

Yea, the whole version thing perplexes me too. When the spreadsheet was being used at the office, with this code, it was working fine. Maybe it was after we upgraded to 2002 that things went haywire.


Thanks again for the help.


Frank

[Zack Barresse]

Family is doing good Frank, thanks for asking.
FD has been pretty busy here. Wildfires poping up all around us, although it's been fairly quiet with those around here as of late. We've had a rash of car fires, actually. Seems like they come in groups. This last January we had 6 structure fires in about a 3 week span - this is when we avg 1 per year generally. EMS is busy as ever. Just took my EMT-I test, will hear from that in a few weeks.

Regarding the code Ken just supplied, when you change the row to 4, does that work? Maybe you could put 'Stop' on it's own line above that and then step through it when you run the code. Turning Autofilter off should be like flipping a switch. One Autofilter per sheet, on or off. On has more options (obviously). Turning it onto a whole row should give you visible drop down arrows on each row header with data (non-null value, <> vbNullString) in it.

[golf4]

Thanks, Zack -

Great to hear the family is doin good. I'll give your suggestion a shot.

Any word on the EMT-1 results? Sending all the positive thoughts from here in Salem. Just be sure to keep your head down & eyes open over there. STAY SAFE, BUD!!!!!

Take care.

[Zack Barresse]

No word on the EMT-I yet. I took it 2.5 weeks ago now. The results should be in, but accessibility is the question. Supposedly one can find them on the internet at the state website, but I have yet to find that. At least that's what my classmate told me where she found her results.