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

Thread: VBA efficiency question - restricting values in a pivot tabl

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

    Default

    I've got a VBA script that works, but I want to make it more efficient. Here's the setup:

    Worksheet "Overall Performance" is a report that is linked to pivot table "Source Pivot" in the "Partner Pivot" worksheet. I also have 2 dropdown's in "Overall Performance" that prompt for a begining date and ending date that I'd like to see the report for. (These dropdowns populate cells "G3" and "G4") Once the user chooses the ending date from the dropdown I run the code below. As you can see there are 3 FOR/NEXT loops that turn on or off the date in the pivot table (therefore the report is automatically changed). This macro takes about 10-15 seconds to run, but I think it could probably be much faster. Any efficiency help would be appreciated.

    Thanks, Will



    Sub ddEnddate_click()

    'The following variables were defined
    'as date:
    ' min_date, max_date, begin_date, end_date, i,j,k

    With ThisWorkbook.Worksheets("Overall Performance")
    begin_date = Range("g3").Value
    end_date = Range("g4").Value
    End With

    min_date = "01/01/2002"
    max_date = "01/31/2002"

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For i = min_date To begin_date - 1
    With ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").PivotFields("RECEIVED_DATE")
    .PivotItems(i).Visible = False
    End With
    Next i


    For j = begin_date To end_date
    With ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").PivotFields("RECEIVED_DATE")
    .PivotItems(j).Visible = True
    End With
    Next j


    For k = end_date + 1 To max_date
    With ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").PivotFields("RECEIVED_DATE")
    .PivotItems(k).Visible = False
    End With
    Next k

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic

    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I don't think there's a huge amount you can do to your code to speed it up. As long as the variables are all defined as the correct type then I cannot think of a way to make any significant improvements using your current methods.

    However, if you wanted to see some serious improvements in performance then here's an idea (but very involved)...

    You can programatically set the PivotCache property of a pivot table. The PivotCache is where all of the data for the pivot table is stored. To set the pivot cache you need to create an ADO (ActiveX Data Objects) recordset and then set the PivotCache property to this. When you create the recordset you can use SQL to return only specific records e.g. records lying between begin_date and end_date. The creation of the recordset, assignment to the pivot cache and refresh of the pivot table would be rapid. I'm not sure if I've explained this very well but if you like I can email you a workbook which demonstrates these techniques.

    Regards,
    D




  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Use:

    ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").ManualUpdate = True

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops, that may need to be False not True



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
  •