Pivot Table Manual Update Not Working

Question

I have a pivot table, and I am trying to select certain pivot items based on values in an array. I need this process to go faster, so I have tried using application.calculation = xlcalculationmanual and pivottables.manualupdate = true, but neither seem to be working; the pivot table still recalculates each time I change a pivot item.

Is there something I can do differently to prevent Excel from recalculating each time?

Here is my code:

Application.Calculation = xlCalculationManual

'code to fill array with list of companies goes here    

dim PT As Excel.PivotTable
Set PT = Sheets("LE Pivot Table").PivotTables("PivotTable1")

Sheets("LE Pivot Table").PivotTables("PivotTable1").ManualUpdate = True

dim pivItem As PivotItem

'compare pivot items to array.  If pivot item matches an element of the array, make it visible=true, otherwise, make it visible=false
For Each pivItem In PT.PivotFields("company").PivotItems
    pivItem.Visible = False 'initially make item unchecked
    For Each company In ArrayOfCompanies()
        If pivItem.Value = company Then
            pivItem.Visible = True
        End If
    Next company
Next pivItem

Michael Michael · Accepted Answer · 2016-09-20T15:45:51

pivottable.ManualUpdate [ = setting ]
True causes RefreshTable to clear data from the pivot table, rather than refreshing it
False allows RefreshTable to work normally.
Default is False.
This property is reset to False automatically after the calling procedure ends (important)

This property should be set to true just before you make an update (e.g. changing pivot item Visible property)
Below is some code written in C# as an example:

    private void FilterByPivotItems(PivotField pf, List<string> pivotItemNames)
    {
        PivotItems pis = pf.ChildItems;

        if (pf.Orientation != 0)
        {
            int oldAutoSortOrder = 0;

            if (pf.AutoSortOrder != (int)Constants.xlManual)
            {
                oldAutoSortOrder = pf.AutoSortOrder;
                pf.AutoSort((int)Constants.xlManual, pf.Name);
            }

            int pivotItemsCount = pf.PivotItems().Count;

            for (int i = 1; i <= pivotItemsCount; i++)
            {
                PivotItem pi = pf.PivotItems(i);

                // check if current pivot item needs to be hidden (if it exists in pivotItemNames)
                var match = pivotItemNames.FirstOrDefault(stringToCheck => stringToCheck.Equals(pi.Value));

                if (match == null)
                {
                    TryFilterPivotItems(pi, false, true);
                }
                else
                {
                    TryFilterPivotItems(pi, true, true);
                }
            }

            if (oldAutoSortOrder != 0)
            {
                pf.AutoSort(oldAutoSortOrder, pf.Name);
            }

            PivotTable pt = pf.Parent as PivotTable;
            if (pt != null)
            {
                // changing a pivot item triggers pivot table update
                // so a refresh should be avoided cause it takes a lot and is unnecessary in this case
                pt.Update();
            }
        }
    }

    private void TryFilterPivotItems(PivotItem currentPI, bool filterValue, bool deferLayoutUpdate = false)
    {
        try
        {
            PivotField pf = currentPI.Parent;
            PivotTable pt = pf.Parent as PivotTable;

            if (currentPI.Visible != filterValue)
            {
                if (deferLayoutUpdate == true && pt != null)
                {
                    // just keep these three lines stick together, no if, no nothing (otherwise ManualUpdate will reset back to false)
                    pt.ManualUpdate = true;
                    currentPI.Visible = filterValue;

                    // this may be redundant since setting Visible property of pivot item, resets ManualUpdate to false
                    pt.ManualUpdate = false;
                }
                else
                {
                    currentPI.Visible = filterValue;
                }
            }
        }
        catch (Exception ex)
        {

        }
    }

    private void TryFilterPivotItems(PivotField pf, string itemValue, bool filterValue, bool deferLayoutUpdate = false)
    {
        try
        {
            PivotItem currentPI = pf.PivotItems(itemValue);
            TryFilterPivotItems(currentPI, filterValue, deferLayoutUpdate);
        }
        catch (Exception ex)
        {

        }
    }

As a conclusion, ManualUpdate property change doesn't stay for long (in my tests, I could see that it gets reset to false as soon as possible, so that's why I recommended you to set it to true whenever you want to make a change for a pivot item)

For more info on what means an update in Excel, you can check the following:
Pivot Refresh vs. Update – is there a real difference?

References:
Title: Programming Excel with VBA and .NET
By: Jeff Webb, Steve Saunders
Print ISBN: 978-0-596-00766-9 | ISBN 10: 0-596-00766-3
Ebook ISBN: 978-0-596-15951-1 | ISBN 10: 0-596-15951-X

Pivot Table Manual Update Not Working

1 Answers