Macro Fails after AutoFilter

Summary: When developing a macro that others may use, you might want to test it out to make sure it works properly if an AutoFilter is in effect. If it doesn't, you can turn off the AutoFilter with a simple single-line command. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

In testing new macros that you develop, you may find that the macro fails when it is run and the information in a worksheet is filtered using the AutoFilter. This can happen because the macro may expect that all the information in the worksheet is available, or it may try to update information that is not visible on the screen.

The best solution to this problem is to make sure that the macro turns off the AutoFilter feature. This can be easily done by adding the following line of code near the beginning of the macro:

ActiveSheet.AutoFilterMode = False

This ensures that AutoFilter is off, and removes the problems that AutoFiltered data may present for your macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3314) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Don't Go in Debt for Christmas! Tired of trying to keep up with the Joneses for Christmas? Want to enjoy the season rather than dread the aftermath? Learn how you can avoid the financial traps that spring up every Christmas.

Check out Top Fifteen Tips for Financing Christmas today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Macro Fails after AutoFilter