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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.
 

Shanehughes

New Member
Joined
Oct 19, 2006
Messages
5
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
 

Felix Atagong

Active Member
Joined
Jun 27, 2003
Messages
359
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...
 

riaz

Well-known Member
Joined
Jun 27, 2006
Messages
779
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
 

Felix Atagong

Active Member
Joined
Jun 27, 2003
Messages
359
I was merely pointing to the post above. I don't have a solution (other than deleting all filters).
 

Forum statistics

Threads
1,141,130
Messages
5,704,452
Members
421,350
Latest member
jake9951

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
Top