Link to home
Start Free TrialLog in
Avatar of petesmithz
petesmithz

asked on

subtotal 109 function on filtered lists with calculate mode manual

I have a new workbook that has a lot of calcualtions - 36 per row and hundered of rows per sheet, about a dozen sheets
Each sheet has subtotals using "109" to reflect the value of selection made in flitered lists. It all worked fine but hopelessly slow.
As a "quick and dirty", I introduced vb to turn off calculation mode on workbook open (and on again on closebefore save), and do a worksheet.calculate on the worksheet activate and change events.
This works fine and is a big improvement in speed EXCEPT the subtotals are not recalculating. the sheet is in Excel 2003 but I've tried it in 2007 with the same results . .

Ideas ?
Avatar of Kanwaljit Dhunna
Kanwaljit Dhunna
Flag of India image

Hi,
Have you tried Pivot Tables ? (Data ->Pivot Tables)
Post the sample sheet if you don't know how to do it.
Avatar of petesmithz
petesmithz

ASKER

Understand what you are saying and I'm fairly good on pivot tables, but this wouldn't work.

All I need to know is why the worksheet change event isn't getting triggered with a selection change on a filtered list . ..
Any sample sheet ?
The code is straightforward. I now see it has nothing to do with the subtotal option, simply that that the worksheet change event isn't being triggred when I change a value in a flitered list - the "stop" dosn't occur - the event doesnt happen.

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
Stop
End Sub

Avatar of Dave

A potential workaround is
1) Use a SUBTOTAL formula on a dummy sheet (I used "dummy") to track the change
2) Set the local sheet properties of all other sheets to manual as below
3) Track the filtering on the Calculate event on "dummy"
4) Run your calculate code on the sheet with the filter
Cheers
Dave
 
 
 
 


Sub AllButOne()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.EnableCalculation = (ws.Name = "dummy")
Next ws
End Sub
 
' On sheet "dummy"
Private Sub Worksheet_Calculate()
With ActiveSheet
.EnableCalculation = True
.Calculate
.EnableCalculation = False
End With
End Sub

Open in new window

dave
this seems very close but I can't get it to work - code is a bit too clever for me !

Could you please explain what it is doing . .
 ,  . where should the first routine be - workbook open.? How does this set the mode to manual ?

 . . why will the routine on dummy work / what event is going to cause recalucalte ? the subtotal 109 doesn't??

Can't but help think this is close but can't quite see it yet


ASKER CERTIFIED SOLUTION
Avatar of Dave
Dave
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Class - got it at last. Thanks
Glad you got it working and thx for the grade :)