[VBA] Adding string to each element in an Array


New Member
Aug 20, 2019
Hi everyone,

I'm new to VBA so I apologise if the terms I'm using are incorrect. I'm trying to filter an OLAP PivotField based on the ranges created in second worksheet. The number of filters to perform depend on the number of columns in that worksheet, and only the items in that column are made visible. Once this is done, it saves the chart that is linked to the pivot table and jumps onto the next column for filtering.

For i = 1 To lastcolumnlastrow = arrayws.Cells(Rows.Count, i).End(xlUp).Row
Set aggrange = arrayws.Range(Cells(2, i), Cells(lastrow, i))
myarray = Application.Transpose(aggrange.Value)

    With pf
        .VisibleItemsList = myarray
    End With

Next i
I've managed to get the code above to work because I manually added the syntax in bold into the columns. But I only want the elements to be visible to the end user so that they don't have to bother with the syntax.

pt.PivotFields("[Property].[Property].[Property]").visibleitemslist = Array("[B][Property].[Property].[All].[[/B]Element1[B]][/B]", "[B][Property].[Property].[All].[[/B]Element2[B]][/B]", ...)

I've figured that I could save them into strings and somehow join them into the front and back of each element in the array in the code. But I can't figure it out or find any similar code on the internet.

str1 = "[Property].[Property].[All].["
str2 = "]"

I hope this isn't confusing and thank you in advance for the help.

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.


MrExcel MVP
Apr 25, 2006
Welcome to the board

See if this helps:

Sub Test()
Dim v As Variant
Dim str1 As String, str2 As String

str1 = "[Property].[Property].[All].["
str2 = "]"

' test array
v = Array("a", "b", "c")

v = Join(v, "|")
v = str1 & Replace(v, "|", str2 & "|" & str1) & str2

' confirm the new elements in the array just for the test
MsgBox v

' rebuild the array
v = Split(v, "|")

' ... use the array

End Sub

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...