AutoFilter & Subtotal Failure - Excel 2002 SP3

Shanehughes

New Member
Joined
Oct 19, 2006
Messages
5
Hi Guys

My problem is an intermittent failure of the AutoFilter function and subtotal formula

About every third occasion when I attempt to release the filter(s) they won’t release. The only cure is to switch off AutoFilter, block the hidden columns and select unhide from the format menu. Even after this, with all data in view, the subtotal formula at the top of my sheet won’t recalculate and continues to show the filtered values. To clear the subtotal problem I have to copy paste the same formula from adjacent columns

It is a complex worksheet with many tabs, but basically only consisting of v & h lookups, if statements and array lookups. None of the formulas are linked to external sheets

Have any of you experienced these problems?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I can't comment on the subtotals issue you raise, but I certainly come across the apparent failure of autofilter. It is extremely irritating and in the end, I gave up trying to resolve it, and went with a little macro in my Personal.xls which I assigned to a toolbar button to switch Autofilter On/Off and unhide all hidden columns. if you are interested, the macro is:

Code:
Sub TurnOffAutofilter()
ActiveCell.EntireRow.AutoFilter
With Cells
    .EntireColumn.Hidden = False
    .EntireRow.Hidden = False
End With
End Sub

I found that the toolbar button natively available via View>Toolbars>Customise would turn autofilter on but not off. Which is nuts. Hence my code.
 
Upvote 0
Hi Richard,

At least i'm glad i'm not on my own! Thanks for the work around. It seems i'll have to learn to use macros! Something i started doing at least a decade ago, found they where (at that time?) ineffective because by the time i'd changed the original and reference files during a normal working day the macro couldn't find where it was supposed to be working. Or maybe it was just me being thick and not anchoring the reference points.

! I think i'll try to include the subtotal workaround within the macro as well

Cheers

Shane
 
Upvote 0
Could this be the solution? I am looking at a spreadsheet (excel 2000) that changes results in the subtotals whenever you visualize the subtotal details or not.

So if you show the details the subtal is (for instance) 1000 $; if you hide the details (with the buttons at the left) some subtotals suddenly switch to zero...
 
Upvote 0
Could this be the solution?

Hi Felix

I would be very interested in that solution, as I am seriously thinking of upgrading the whole office to Excel 2003 just for this one function - expensive solution, apart from the fact that 2003 is no longer available here for love nor money.

Your post did not provide any links. Would you mind putting them up?

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top