Clearing Filters and Returning to Cell A1

**DentonHTHS** · 02-27-2009, 02:09 PM

I am not sure if this is doable in VB or if there is an excel function to do this but I am using a rather large worksheet that is shared amongst my staff. I would like to have the sheet clear any filters that have been used by one member and for the sheet to save with the cursor in the home cell (A1). This way the sheet is ready for the next user. Can this be done with VB?

I have never used VB before in Excel. New territory for me so any hand-holding explanations would be greatly appreciated.

**VBA Noob** · 02-27-2009, 02:34 PM

Try this event macro.

Code goes in ThisWorkbook module. Save the code. Open file and user will need to enable macros

http://www.contextures.com/xlvba01.html#Workbook

Please Login or Register  to view this content.

VBA Noob

**DentonHTHS** · 02-28-2009, 01:19 AM

Thanks for the code, VBA Noob. I think I inserted correctly but I am still having problems which may have been due to my explanation and not your code. When I save the file and reopen it, my filters disappear (which I suppose is a clear). What I meant is that I wanted the filters to reset so that the next person could filter based on their needs. Sometimes my less than knowledgeable staff get confused when the workbook opens up and its not back at cell A1 and there are several filters that have been left behind when the file was saved by the previous user.

So, I would like the filters to stay but that they would be reset to not presently filtering data when the workbook is open. I'd also like the workbook to always have the cursor in cell A1 when it opens. I couldn't seem to get this code to do that.

Thanks for all the help!

**VBA Noob** · 02-28-2009, 03:39 PM

Replace this line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

See link for more
http://www.contextures.com/xlautofilter03.html#All

VBA Noob

**DentonHTHS** · 03-02-2009, 11:36 AM

Thanks, VBANoob. The filter resetting is now working when I close and reopen the file but it doesn't seem to be putting the cursor at the home cell. I wonder if I am doing some thing wrong? This is how I pasted the code into my workbook.

Please Login or Register  to view this content.

**VBA Noob** · 03-02-2009, 02:06 PM

Works for me.

See attached

You could try a sheet activate macro also in ThisWorkbook so when a user selects a sheet it automatically goes to A1

Please Login or Register  to view this content.

VBA Noob

**DentonHTHS** · 03-02-2009, 05:08 PM

Would saving the workbook change the behaviour of the code? Some of my users save the file before closing (even though they don't have to). If the same user then reopened the book would it open in the position it was saved or would the code override that?

I think I will put this new code in that you suggested. Should it go before or after the previous code or does it matter? Thanks for your patience with the noobie.

**VBA Noob** · 03-02-2009, 05:26 PM

Would saving the workbook change the behaviour of the code?

No as only runs when workbook is open (2nd code when a sheet is selected)

If the same user then reopened the book would it open in the position it was saved or would the code override that?

Code should override it. It loops from the last sheet to the first unselect the filter and should select cell A1 for that workbook...Does for me

Should it go before or after the previous code or does it matter?

Doesn't matter as long as it's in ThisWorkbook module.
Only problem is it won't work for the activesheet only when they select the first sheet

VBA Noob

**DentonHTHS** · 03-02-2009, 06:21 PM

I saved the file you attached after scrolling down to row 90 or so. When I reopened the file it opened up at that position. As soon as I hit a direction key it did go up to the top of the sheet. Not sure why I am getting this behaviour. Still if a keystroke will get the sheet to return to the top that will help a lot with people getting lost in rows and columns.

Thanks very much for your help!