VBA efficiency question - restricting values in a pivot tabl

wcassell

New Member
Joined
Mar 9, 2002
Messages
24
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top