• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table Macro - Filter out anything containing one of two values [SOLVED]

T

t33p33

New Member
I am trying to write a macro that will filter a pivot table. I want to filter out anything that contains one of two values; "Hosting", and "Infrastructure". I am getting an error on the following macro. It will only filter out the first value. I have tried multipe versions and cannot seem to find a way to filter out anythign containing two different values. Any assistance would be appreciated.


Sub testFilter()

Dim PTitle As PivotField

Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")

With PTitle

.ClearAllFilters

.PivotFilters.Add xlCaptionContains, , "Hosting"

.PivotFilters.Add xlCaptionContains, , "Infrastructure"

End With

End Sub


I error out when I get to the second filter. I can get this to work with one filter, but not to filter out two different values.
 
Hi t33p33!


Welcome to the forum..


We are glad that you are here..


can you please check the below code..

[pre]
Code: 
Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
pitem = "Hosting|Infrastructure"
Application.ScreenUpdating = False
With PTitle
.ClearAllFilters
For i = 1 To PTitle.PivotItems.Count
If InStr(1, pitem, PTitle.PivotItems(i), vbTextCompare) > 0 Then
PTitle.PivotItems(i).Visible = True
Else
PTitle.PivotItems(i).Visible = False
End If
Next i
End With
End Sub
[/pre]

Please let us know your feedback.. :)


Regards,

Deb
 
Deb,


Thank you for your help!


I get a Run-time 1004 error "Unable to set the Visible property of the PivotItem class" occurring on the [PTitle.PivotItems(i).Visible = False] line.


It also filtered out everything except one result, but that result contained Infrastructure Support Agreement in the project title.
 
Hi T33p33!


OK.. so Pivot Item's are not exact "Hosting" or "Infrastructure", pivot item only contain any of these word..


Then try this..

[pre]
Code: 
Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
'pitem = "Hosting|Infrastructure"
Application.ScreenUpdating = False
With PTitle
.ClearAllFilters
For i = 1 To PTitle.PivotItems.Count
If InStr(1, PTitle.PivotItems(i), "Hosting", vbTextCompare) > 0 _
Or InStr(1, PTitle.PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
Then
PTitle.PivotItems(i).Visible = True
Else
PTitle.PivotItems(i).Visible = False
End If
Next i
End With
End Sub
[/pre]

Regards,

Deb
 
I am still getting the same error on this row [PTitle.PivotItems(i).Visible = True]


You are correct though, the pivot item only contains the words and may not be an exact match.


Thanks for your continued help!
 
Better to upload :(

http://chandoo.org/forums/topic/posting-a-sample-workbook


or you can mail me the same..

mail3debraj[at]gmail[dot]com
 
@SirJB7

Hi, myself!

So long...

Ha!... this new kid in the block now posts his email address (trying to flawlessly avoid robots and crawlers, IMHO)... I still remember the old days... no, not when internet didn't exist yet... these days:

http://chandoo.org/forums/topic/about-vba?message=spammed#post-36383

Regards!
 
Hi t33p33..


I am little bit confused.. can you please help me..


Can you please check any project with # number..

ie.


Business Infrastructure ContinuityProject#810

Pivot table is displaying this as

Code: 
Business Infrastructure ContinuityProject#810 


however when I am reading it in VBA its displaying it as

[code]Business Infrastructure ContinuityProject#810 (Distributed Systems Services)


I checked all sheet.. but not able to found a clue..

Did you done something.. with Pivot Table Formatting.. !!


By the way, Issue has been checked.. but still not rectified.. I have forcefully inform to ignore this..


check this one..

[pre]Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
Application.ScreenUpdating = False
With PTitle
.ClearAllFilters
For i = 2 To .PivotItems.Count
If InStr(1, .PivotItems(i), "Hosting", vbTextCompare) > 0 _
Or InStr(1, .PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
Then
On Error Resume Next
.PivotItems(i).Visible = True
On Error GoTo 0
Else
PTitle.PivotItems(i).Visible = False
End If
Next i
End With
End Sub[/code][/pre]

Regards,

Deb
 
Deb,


Thank you again! This runs perfectly! The only change would be that I want to show everything except those items that have Hosting or Infrastructure in the title.


In regard to the names I thought I deleted them out, but apparently that did not truly remove them, so VBA picked it up. Oops.
 
Hi Deb. Looking at your code, I believe you don't need these lines:

On Error Resume Next

.PivotItems(i).Visible = True

On Error GoTo 0

...because all items are already visible, and there wouldn't be an error in any case.


Ad a question: do you start from 2 so that you don't accidentally try and hide all pivot items? If so, you probably should also check whether .PivotItems(1) contains the search terms or not.


t33p33: This should do it

[pre]
Code: 
Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
Application.ScreenUpdating = False
With PTitle
.ClearAllFilters
For i = 1 To .PivotItems.Count
If InStr(1, .PivotItems(i), "Hosting", vbTextCompare) > 0 _
Or InStr(1, .PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
Then
PTitle.PivotItems(i).Visible = False
End If
Next i
End With
End Sub
[/pre]
Note that if your pivottable is really big, iterating through the items one at a time can take a while. If this is the case, let me know, as I've got some code that takes a very sneaky approach that takes seconds, rather than the minutes it takes on a large pivot table.
 
This works!


I do have a pretty large data set so if you have another approach that will speed up the process I would welcome the information!


Thank you both for your help! It is greatly appreciated!
 
No problem. I'll need to change my other code a bit, and will post here when I get a chance. Might be a few days.


Note that when you are working with pivots, you generally want to set the .ManualUpdate property to True while your code does its thing, and then set it back to false again when you're done. This will dramatically speed up a routine, because otherwise the pivot will recalculate after each filter item is changed.

For instance, with my test code, I just filtered a pivotfield with 5000 items. With .ManualUpdate = true this took 4 seconds. With .ManualUpdate = false this took 24 seconds!


In fact, you might as well temporarily turn off workbook calculation too:

[pre]
Code: 
Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End with

ActiveSheet.PivotTables(1).ManualUpdate = True

With PTitle

.ClearAllFilters
For i = 1 To .PivotItems.Count
If InStr(1, .PivotItems(i), "Hosting", vbTextCompare) > 0 _
Or InStr(1, .PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
Then
PTitle.PivotItems(i).Visible = False
End If
Next i
End With

With Application
.ScreenUpdating = true
.Calculation = xlCalculationAutomatic
End with

ActiveSheet.PivotTables(1).ManualUpdate = false
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top