Solved

VBA for Excel to filter pivot table EXCLUDING "text"

Posted on 2016-08-10
4
47 Views
Last Modified: 2016-08-10
I have a basic question on how to EXCLUDE an item from a Pivot Table Filter.

A snippet of my code is below
    Set myPivotTableField = myPivotTable.PivotFields("Category Filter")
    myPivotTableField.Orientation = xlPageField
    myPivotTableField.Position = 1
    Set myPivotTableField = myPivotTable.PivotFields("Category Filter")
    myPivotTableField.CurrentPage = "Cars"

Open in new window


Now this will Filter "Cars" from the Category Filter.

However, I would like to EXCLUDE "Cars" from the filter
Something Like : myPivotTableField.CurrentPage <> "Cars"

But this syntax does not work.   Is there an easy way to NOT include "Cars" ?

Thanks
0
Comment
Question by:Mchallinor
  • 2
4 Comments
 

Author Comment

by:Mchallinor
ID: 41750215
How do I convert the following to a VBA syntax?
   
 
With ActiveSheet.PivotTables("PivotTable").PivotFields("Category Filter")
        .PivotItems("Cars").Visible = False

Open in new window

0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41750216
That looks like it already is VBA syntax.
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41750228
HI,

Maybe

For Each pvtItm In myPivotTableField.PivotItems
    If pvtItm.Name <> "Cars" Then
        pvtItm.Visible = True
    Else
        pvtItm.Visible = False
    End if
Next

Open in new window

Regards
0
 

Author Closing Comment

by:Mchallinor
ID: 41750234
Set myPivotTableField = myPivotTable.PivotFields("Category Filter")
    myPivotTableField.EnableMultiplePageItems = True          
    myPivotTableField.PivotItems("Cars").Visible = False


After posting I worked out my own answer!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now