VB formula's

Posted on 2012-09-19
Last Modified: 2012-09-22
Is there a way I can refresh the formula's via a command button? I wanted to be able to refresh & then paste the values so that  the formula's would only update upon command.

I read that the subtotal function only extends to a 1000 lines, so have had to add the "60000" reference.

So this is one way I thought would help solve the lag problem when the filter is 'reset'

X2 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=5))

X3 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=4))

X4 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=3))

X5 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=2))

X6 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=1))

AA2 =SUBTOTAL(3,D9:D60000)

AA3 {=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9:F60000,ROW(F9:F60000)-MIN(ROW(F9:F60000)),,1)),--(F9:F60000="email"))&" Email"}

AA4 {=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9:F60000,ROW(F9:F60000)-MIN(ROW(F9:F60000)),,1)),--(F9:F60000="Vantive"))&" Vantive"}

AA5 {=INDEX(H9:H60000,MATCH(MAX(COUNTIF(H9:H60000,H9:H60000)),COUNTIF(H9:H60000,H9:H60000),0),)}

AA6 {=INDEX(I9:I60000,MATCH(MAX(COUNTIF(I9:I60000,I9:I60000)),COUNTIF(I9:I60000,I9:I60000),0),)}
Question by:Kiwi-123
    LVL 85

    Expert Comment

    by:Rory Archibald
    I think you may be confusing the fact that the autofilter dropdown only shows 1000 items (in xl2003 and prior) - SUBTOTAL works on whatever range you tell it to. I wouldn't recommend including more rows than necessary in formula like that.

    What you may do is set calculation to manual and then use the Calculate method of the Range object:

    Open in new window

    for example.

    Author Comment

    Thanks Rorya, some good advice there.


    Is there an example you could show me? How would I set the calculation to manual?
    LVL 85

    Accepted Solution

    application.calculation = xlcalculationmanual
    or simply through the UI. ;)

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now