How to create an Array in VBA

[largeselection]

Hello,

I'm having some difficulty wrapping my mind around how is best to structure this so hopefully someone can point me in the right direction or send me a link to some useful information.

Basically I want to create some code which will change the elements in a pivot table filter- So I have say 200 players and I currently have a pivot table which displays information and "PLAYER" is a field in the filter. I want to be able to display only the information in the pivot table for say 25 players only so currently I can go into the filter and manually select the 25 I want to see and deselect everyone else. The issue is that I may want to see different groups and I'd like it to be faster than that manual work. So I thought it might be possible to have the filter adjust programatticaly based on a list of cells- that way I can just paste the PLAYER #s I want to see in a range and have the table filter without having to click each one individually.

So I know at some point I will use:

ActiveSheet.PivotTables("DB1").PivotFields( _
"[PLAYER].[PLAYER].[PLAYER]").VisibleItemsList = Array(........

in order to do the actual filtering so what I need help with is how to fill the array.

All the elements in the array take the form "[PLAYER].[PLAYER].&[###]" so if I record while I select two players the resulting code is:

...VisibleItemsList = Array("[PLAYER].[PLAYER].&[471]", "[PLAYER].[PLAYER].&[472]")

so if I have a range of cells (A1:Axxx) how can I have it create an array that basically says set the Array = "[PLAYER].[PLAYER].&[A1]", "[PLAYER].[PLAYER].&[A2]", "[PLAYER].[PLAYER].&[Axxx]", from A1 until the next cell in column A is blank?


Sorry for the long post, but I wanted to include all the details. TIA for any help/direction.

 

[largeselection]

So I've done some digging and have got this so far so please let me know if you think I'm on the right track...

Dim MyArray as Variant
Dim rcnt as integer
 
rcnt = Range(Range("A2"),Range("A1000").End(xlUp)).Count
 
MyArray = Range("A2:A" & rcnt & "").Value
 
ActiveSheet.PivotTables("DB2").PivotFields("[PLAYER].[PLAYER].[PLAYER]").VisibleItemsLIst = MyArray

This doesn't work. The array seems to be OK, because if I do MsgBox MyArray(1,1) I get the value in cell A2, MyArray(2,1) gives me the value in cell A3, etc. However I keep getting an application or object defined error.

Is it because it can't interpret the array correctly? I know when I did the recorder it was Array(first entry, second entry, third entry, etc) so maybe the way I've constructed the array it is
first entry
second entry
third entry
etc

so maybe that is tripping it up? Do I need to transpose it and then convert it to a value string?

 

[Andrew Poulsom]

I can't test it, but maybe you need to Transpose the array.

 

[largeselection]

I've tried changing to:

MyArray = Application.Transpose(Range("A2:A" & rcnt & "").Value)

But it's still not filtering the pivot table if I try and Use MyArray

Should I try doing something with a string instead?

Maybe something like

Dim MyString as String
 
Do While not isblank(ActiveCell)
Range("A2").Select
MyString = MyString & ActiveCell.Value
ActiveCell.Offset(1,0).Select
Loop
 
ActiveSheet.PivotTables("DB2").PivotFields("[PLAYER].[PLAYER].[PLAYER]").VisibleItemsList = Array(MyString)

??

I'm sure that's not written correctly, but I think you see what I"m thinking of

 

[Andrew Poulsom]

Try transposing it when you assign it to the VisibleItemList property (not when you create it).

 

[largeselection]

So now it looks like this:

In cells A2:A4 I have
<TABLE style="WIDTH: 139pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=185><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=185>"[PLAYER].[PLAYER].&[473]"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>"[PLAYER].[PLAYER].&[7945]"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>"[PLAYER].[PLAYER].&[8464]"</TD></TR></TBODY></TABLE>

Then the sub looks like:

Dim MyArray as Variant
Dim rcnt as integer
 
rcnt = Range(Range("A2"), Range("A1000").End(xlUp)).Count
 
MyArray = Range("A2:A" & rcnt & "").Value
 
ActiveSheet.PivotTables("DB2").PivotFields("[PLAYER].[PLAYER].[PLAYER]").VisibleItemsList = Application.Transpose(MyArray)

And that still gives me application or user defined error.

I have also tried it where the entries in Range A2:A4 have commas at the end as well like
<TABLE style="WIDTH: 139pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=185><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=185>"[PLAYER].[PLAYER].&[473]",</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>"[PLAYER].[PLAYER].&[7945]",</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>"[PLAYER].[PLAYER].&[8464]"</TD></TR></TBODY></TABLE>

and I have tried VisibleItemsList = Array(Application.Transpose(MyArray))

with no success either.

I also tried creating a string with a function which is a concatenation of the range with commas in between each and that creates a string which looks exactly like the recorded version (I checked via msgbox), but if I put that in the visible items list that doesnt work either.

 

[Andrew Poulsom]

Sorry, I don't think I can help any further because I don't have an OLAP cube to play with. Maybe it works like AutoFilter:

http://www.mrexcel.com/forum/showthread.php?t=429788

 

[Andrew Poulsom]

I created an OLAP cube and a pivot table. The Report Filter name was [Country].[Country]. If I recorded a macro while selecting the first 3 items I got:

Sub Macro1()
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Country].[Country]"). _
        VisibleItemsList = Array("[Country].[All].[Argentina]", _
        "[Country].[All].[Austria]", "[Country].[All].[Belgium]")
End Sub

So I put those 3 items in A1:A3:

<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=175><COLGROUP><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6400" width=175><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 131pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=175>[Country].[All].[Argentina]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>[Country].[All].[Austria]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 131pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=175>[Country].[All].[Brazil]</TD></TR></TBODY></TABLE>

and this code worked fine:

Sub Test()
    Dim MyArray As Variant
    MyArray = Application.Transpose(Range("A1:A3").Value)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Country].[Country]").VisibleItemsList = MyArray
End Sub

 

[largeselection]

Thanks so much for going through that extra effort! I adjusted it and it works just fine now!

The issue was that I had the quotation marks included in the cells:
<TABLE style="WIDTH: 139pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=185><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 139pt; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=17 width=185>"[PLAYER].[PLAYER].&[473]"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=17>"[PLAYER].[PLAYER].&[7945]"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=17>"[PLAYER].[PLAYER].&[8464]"</TD></TR></TBODY></TABLE>

So I took out all the quotation marks and now it works just fine.

Thank you again!