VBA to EXclude pivotitems in namedrange

[dellzy]

Hello Excel Experts,

I found several amazing macros from amazing excel experts to filter pivotitems, but it covers to INCLUDE pivotitems in namedrange or after minor change, I can get to EXCLUDE pivotitem but only 1 item or multiple items if I hardcode them all as fixed in VBA. However, what I really need is for it to be able to filter out by namedrange because the input may change from time to time because the input is holidays dates throughout the year.
Below are my findings so far. Perhaps someone expert can tweak some lines to meet my objective:-

[COLOR=#333333]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub TestINclude()
    Dim PI As PivotItem
    With Worksheets("Sheet2").PivotTables("PivotKPI").PivotFields("AssignDt")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("cuti"), PI.Name) > 0
        Next PI
    End With
End Sub</code>[/COLOR]

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub TestEXclude()
    Dim PI As PivotItem
    With Worksheets("Sheet2").PivotTables("PivotKPI").PivotFields("AssignDt")
        .ClearAllFilters
        'For Each PI In .PivotItems
         '   PI.Visible = WorksheetFunction.CountIf(Range("cuti"), PI.Name) > 0
        'Next PI
        .PivotItems(Range("cuti")).Visible = False
    End With
End Sub</code>

Appreciate your expertise.

Thank you in advance.

DZ

 

[Domenic]

Try...

[COLOR=#333333]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">PI.Visible = (WorksheetFunction.CountIf(Range("cuti"), PI.Name) </code>[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;"></code><code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">= </code>[COLOR=#333333]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">0)</code>[/COLOR]

Hope this helps!

 

[dellzy]

WoW! Just one symbol "=" to do that magic! Tqvm Domenic!! Yeayy!

Try...

[COLOR=#333333]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">PI.Visible = (WorksheetFunction.CountIf(Range("cuti"), PI.Name) </code>[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;"></code><code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">= </code>[COLOR=#333333]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">0)</code>[/COLOR]

Hope this helps!