Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Recalculate after filter/sort

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone help me with the following problem?

    I have a large workbook with on one sheet a table with an autofilter. When I use this autofilter to select certain records or when I sort it, the whole workbook is recalculated (it turns out that sorting/filtering triggers the recalculation). This takes quite a long time, while it is not necessary. How can I get round this problem?

    Possible solutions:
    - set the calculation mode to manual...but I still want to calculate some cells (eg the SUBTOTAL formulas on the same sheet)...
    - use of the calculate event...but I can only avoid recalculation of the whole workbook by entering code that sets the calculation mode to manual, but then it won't update anything anymore and also the calculate event won't be triggered in the future...
    - use of the filter or sort event...but it does not exist (?)...

    The problem does not seem too difficult, but I still have not been able to solve it.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Depending on how many different cells you are wanting to calculate along with the filter, you may want to try this.
    Place a button on your sheet which will disable calculation for the entire application. Perform your filtering. Then on another button, a macro which selectively calculates your chosen ranges and will also re-enable calculation altogether.
    If your autofilter performs the same operation with the same parameters each time, you could just automate the whole process into one procedure if you like.
    Am I understanding you?
    Thanks,
    Tom

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TsTom, thanks for answering!

    If you use "xlcalculationmanual" and "xlcalculationautomatic" to disable and enable calculation it will finally recalculate the whole workbook anyway, when clicking the second button (or is this not what you mean?).

    Moreover, to keep the sheet user-friendly, it is not preferable to ask the user to click one or two buttons whenever he sorts or autofilters.

    But thanks for helping me further with your solution.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe I should rephrase my question into a more general one:

    Are the default events as defined by visual basic (beforedoubleclick, open, change etc.) the only ones available or is it possible to add new ones yourself in some way? In my case, is there a way to trap an autofilter event or a sort event?

    Thanks

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculate after filter/sort

    How does one "selectively calculate" formulas?
    Excel 2003 and 2010 on 64bit Windows-7

  6. #6
    New Member
    Join Date
    Feb 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculate after filter/sort

    Quote Originally Posted by UHsoccer View Post
    How does one "selectively calculate" formulas?
    You can try either SHIFT+F9 to recalculate the active sheet only, or you can write a simple macro to recalculate only the selected cells:

    Code:
    Sub rangecalc()
    selection.Calculate
    End Sub
    I have that in my macro workbook linked to CTRL+J.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •