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

Make Home Buying Less Stressful! Why make home buying harder than it needs to be? Put your mind at ease—discover all the questions you need to ask to make the best buying decision.

Check out Buying a Home Checklist today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Macro Fails after AutoFilter