try this set sheet calculation to manual
'application.Calculation=xlCalculationManual (VBA) sheet calculates only when the user presses the Shift + F9 button. On Mon, Jun 27, 2011 at 6:08 PM, crossy75 <sjc5...@yahoo.co.uk> wrote: > I have this bit of code in a spreadsheet - it is used with a formula > to count the number of visible rows. This is useful in accounts where > people hid things thus counting what you see isnt always what you get! > > 'Function Vis(Rin As Range) As Range > > 'Returns the subset of Rin that is visible > 'Dim Cell As Range > 'Application.Volatile > 'Set Vis = Nothing > 'For Each Cell In Rin > 'If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then > 'If Vis Is Nothing Then > 'Set Vis = Cell > 'Else > 'Set Vis = Union(Vis, Cell) > 'End If > 'End If > 'Next Cell > 'End Function > > 'Function COUNTIFv(Rin As Range, Condition As Variant) As Long > 'Same as Excel COUNTIF worksheet function, except does not > count > 'cells that are hidden > 'Dim A As Range > 'Dim Csum As Long > 'Csum = 0 > 'For Each A In Vis(Rin).Areas > 'Csum = Csum + WorksheetFunction.CountIf(A, Condition) > 'Next A > 'COUNTIFv = Csum > 'End Function > > > anyway the problem is this code seems to run the whole time - thus i > would like a way to have it deactivated until it comes to do do > something ie press a button, and then the function is activated, the > screen refreshes and i can print or do whatever. > > then switch it off again so the more complex macros etc can run faster > (and smoother) without this getting in the way. > > many thanks > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- Regards Vasant skype Id: vasantjob vasant...@gmail.com -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
