Excel VBA Referencing Defined Range

Question

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

try sheet1.range("definedvar") if it's defined at the worksheet level instead of the workbook level — nutsch
The range is defined at the workbook level through the name manager (not in VBA) — lolatu2
How did you test that the range had nothing in it? Did you debug.print range("definedvar").address? — nutsch

Motes Motes · Accepted Answer · 2013-11-20T21:41:18

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

Excel VBA Referencing Defined Range

1 Answers