Help to Optimize this vba code

[jajamb]

Hello.

Can anyone help me optimize this:

At the moment the range is from D6:D16, but if it gets longer, lets say D6:D100 this code is not so good.

So any ideas?

Sub Macro2()


Dim Filter(1 To 11) As String


Filter(1) = Sheets("Slicercellvalue").Range("D6").Value
Filter(2) = Sheets("Slicercellvalue").Range("D7").Value
Filter(3) = Sheets("Slicercellvalue").Range("D8").Value
Filter(4) = Sheets("Slicercellvalue").Range("D9").Value
Filter(5) = Sheets("Slicercellvalue").Range("D10").Value
Filter(6) = Sheets("Slicercellvalue").Range("D11").Value
Filter(7) = Sheets("Slicercellvalue").Range("D12").Value
Filter(8) = Sheets("Slicercellvalue").Range("D13").Value
Filter(9) = Sheets("Slicercellvalue").Range("D14").Value
Filter(10) = Sheets("Slicercellvalue").Range("D15").Value
Filter(11) = Sheets("Slicercellvalue").Range("D16").Value


    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[5Ansvar].[AnsvarKey].[AnsvarKey]").ClearAllFilters
        
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[5Ansvar].[AnsvarKey].[AnsvarKey]").VisibleItemsList = Array( _
        "[5Ansvar].[AnsvarKey].&[" & Filter(1) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(2) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(3) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(4) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(5) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(6) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(7) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(8) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(9) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(10) & "]", _
        "[5Ansvar].[AnsvarKey].&[" & Filter(11) & "]")
End Sub

 

[Worf]

Hello
For the first part you could do something like:

For i = 1 To 11
    Filter(i) = Sheets("Slicercellvalue").Cells(5 + i, 4).Value
Next

I can´t go much further because I do not use OLAP, but see if this thread helps:

http://www.mrexcel.com/forum/excel-...ications-code-update-pivot-table-filters.html