What is the best way to set the Visible Property of Excel's PivotTable PivotItems to False?

Add-in Express™ Support Service
That's what is more important than anything else

What is the best way to set the Visible Property of Excel's PivotTable PivotItems to False?
Looping in MyAddin1 AddinModule.vb is very slow compared to manual editing and VBA 
bttfa




Posts: 3
Joined: 2010-02-10
My workbook has a worksheet with 200,000 rows. My code creates a PivotTable. There are many rows (unique dates) in the PivotTable. I want to hide most of them (I only want to see a recent week of data). Manually unchecking those dates in the PivotTable is fast. Using VBA is even faster. But looping in VB inside my AddinModule.vb is extremely slow. Here is an excerpt from my code;

Dim pvt As Excel.PivotTable = wsj.PivotTableWizard( _
  SourceType:=Excel.XlSourceType.xlSourceSheet, _
  SourceDat a:=wsj.UsedRange, _
  TableDestination:=wsPivot.Range("a4"), _
  TableName:="PivotDollars") 'Creates PivotTable

Dim pvtRow As Excel.PivotField = CType(pvt.PivotFields("InvoiceDate"), Excel.PivotField) 'Row
With pvtRow
  .Orientation = Excel.XlPivotFieldOrientation.xlRowField
  .Position = 1
End With

...(column and data created - removed for brevity)...

Dim DisplayStartDate As DateTime = DateValue("5/1/2009")
Dim DisplayEndDate As DateTime = DateValue("5/7/2009")
Dim pvtRowItems As Excel.PivotItems = CType(pvtRow.PivotItems, Excel.PivotItems)
With ExcelApp
  .Calculation = Excel.XlCalculation.xlCalculationManual
  .ScreenUpdating = False
End With
For Each pi As Excel.PivotItem In pvtRowItems 'this is taking 3 seconds per loop!
  If DisplayStartDate > DateValue(pi.Value) Or DisplayEndDate < DateValue(pi.Value) Then
    Diagnostics.Debug.Print("pi.value = " & pi.Value)
    pi.Visible = False
  End If
Next
...(ExcelApp reset, etc.)...


What is the fastest way to edit the Visible Property? Was this covered in the sample code in your FAQ (sorry if I missed it)?

Thanks in advance.
Posted 15 Feb, 2010 00:43:01 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hi bttfa,

BTW, what's your name?

I'm sorry, this isn't a frequently asked question.

For Each pi As Excel.PivotItem In pvtRowItems 'this is taking 3 seconds per loop! 
  If DisplayStartDate > DateValue(pi.Value) Or DisplayEndDate < DateValue(pi.Value) Then 
    Diagnostics.Debug.Print("pi.value = " & pi.Value) 
    pi.Visible = False 
  End If 
Next 


Are you saying that a similar code in VBA works significantly faster? If yes, can you please me some code for testing?


Andrei Smolin
Add-in Express Team Leader
Posted 15 Feb, 2010 11:39:23 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Oh, you can send me the code to the support e-mail address (see readme.txt).


Andrei Smolin
Add-in Express Team Leader
Posted 15 Feb, 2010 13:41:50 Top