Excel - Auto-filter and Macros

  The Ghost of Inept Pig 20:04 02 Feb 2005

When using Auto-filter in Excel, every once in a while the filtered items refuse to tally (normally reads something like 200 out 550), and I’m not sure how to get it back – why does this happen? And how do I get my beloved numbers back?

Copying and pasting the data into a new sheet and applying the auto-filter again seems to work, but obviously as the sheet gets bigger (and more formatted) – I’d much rather that I could tweak the buttons and make it work again.

Also, when I’ve had Macros on a spreadsheet and then deleted them (for various reasons) I’m still prompted by Excel whether I want to ‘Enable’ or ‘Disable’ the Macros, is there anyway to stop Excel from asking this – there are no Macros left to delete from the list, and I need to keep the security on Medium (work policy – or some such)

  VoG II 20:09 02 Feb 2005

Have you tried removing the AutoFilter then re-applying?

Have you deleted all of the macro modules?

  The Ghost of Inept Pig 20:39 02 Feb 2005

Thanks for replying VoG.

I've tried removing and the reapplying the auto-filter, but the numbers fail to reappear at the bottom - there's also a 'sum' option that seems to be selectable from the bottom bar, and that's ticked too.

I've deleted all the Macros I can see, and they're no longer used on the sheet - but the warning still appears.

Any ideas?

  VoG II 20:44 02 Feb 2005

Press ALT+F11 to open the Visual Basic Editor. In the left hand pane double-click on Modules then select each one and use File/Delete module.

If you don't see any Modules then I am totally baffled.

I don't know why the AutoFilter is misbehaving I'm safraid. Is this with more than one file?

  The Ghost of Inept Pig 20:54 02 Feb 2005

I shall check the Visual Basic Editor, the Auto-filter misbehaves on this one particular spreadsheet - which is a bit of a pain as it is the one that I use the most, as I said, copying the data to a new spreadsheet and then applying the Auto-filter seems to work - but that doesn't explain why it shouldn't work on the existing one.

Thanks for the suggestions though

  The Ghost of Inept Pig 20:14 03 Feb 2005


No sign of the Macros.

And the Auto-filter is affected by my use of the CONCATENATE command, so I've dropped that from the spreadsheer and all is well.

Just in case anyone wondered

  The Ghost of Inept Pig 15:11 24 Mar 2006

Slightly over a year later and I run into the problem again!

I'm aware that I've ticked this topic as resolved, but just thought I'd see if anyone had come across why this happens yet? And more importantly, how to fix it?

The current spreadsheet has a few SUM calculations, but is nothing special - any ideas why Auto-filters counting seems to come and go as it pleases?

Copy and pasting the data into a new sheet still fixes the problem - but it's rather a pain.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

How Sketch and InVision have revolutionised our design workflow

The best tech gifts for Christmas 2017

Les meilleurs jeux de société (2017)