CODEWITHSUNDEEP
vbaexcel
lolatu2
lolatu2

Reputation: 383

Excel VBA Referencing Defined Range

In the Excel worksheet, I defined say range A1:A10 as "definedvar". I'm trying to use the values in this range to drive pivot table filters. I can't figure out how to reference the defined range values I want. I stepped through the code and the code does go through the pivot table items like I want, but the "definedvar" values has nothing in it. What am I doing wrong?

    For Each pi In pt1.PivotFields("COLUMN A").PivotItems
    If IsError(Application.WorksheetFunction.Match(pi.Name, Range("definedvar"), 0)) Then
        pi.Visible = False
    Else
        pi.Visible = True
    End If
    Next pi

Upvotes: 0

Views: 222

Answers (1)

Motomotes
Motomotes

Reputation: 4237

Try ActiveWorkbook.Names.Item("definedvar").RefersToRange

For Each pi In pt1.PivotFields("COLUMN A").PivotItems
If IsError(Application.WorksheetFunction.Match(pi.Name, ActiveWorkbook.Names.Item("definedvar").RefersToRange.Value, 0)) Then
    pi.Visible = False
Else
    pi.Visible = True
End If
Next pi

Upvotes: 0

Related Questions