[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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.


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

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...