Creating an array in VBA from spreadsheet data

Question

I have a spreadsheet of data that I want to put into a VBA array which then outputs unique values to a new sheet. I have got that to work so far. However, some of the cells in the original data have text separated by commas, and I want to add those to the array as well. I can't quite get that bit to work.

After the various 'dims', my code is

'Grabs the data to work with
Set rTable = Worksheets("Data Entry").Range("N1:N100", "P1:P100")
'Puts it into an array
MyArray = rTable.Value

'Sets where the data will end up
Set rCell = Worksheets("TestSheet").Range("A1:A100")


'Each unique entry gets added to the new array
On Error Resume Next
For Each a In MyArray
UnqArray.Add a, a
Next

'Add unique data to new location
For i = 1 To UnqArray.Count
rCell(i, 1) = UnqArray(i)
Next

I have tried doing a new variant to store the split data

SpArray = split(MyArray,", ")

and then have that here

MyArray = rTable.Value
SpArray = split(MyArray,", ")

and then refer to SpArray for the rest of the code

I've also tried to have as part of

For Each a in SpArray

but it doesn't work for me.

Do I need to do a separate loop on each cell of the array before I filter out the unique ones?

It isn't clear what you're trying to achieve. Please show some example input, desired output, current output, and point out how the current output differs from the desired. [A Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) always helps. — Jean-François Corbett, Mar 09 '15 at 13:37
see if [THIS](http://stackoverflow.com/a/18481730/2140173) helps — , Mar 09 '15 at 13:38

score 0 · Accepted Answer · answered Mar 09 '15 at 13:54

Yes, you need another loop. But if you set a reference to Microsoft Scripting Runtime and use a Dictionary object, you can eliminate the loop that writes to the range because Dictionary.Keys returns an array.

In this example, it attempts to split every entry on a comma and treats each of those as a unique. If there is no comma, Split returns the one value so it works in both cases. There's probably a small cost to splitting things that don't need to be split, but you won't notice until your range is much larger. And it makes the code cleaner, I think.

Sub WriteUniques()

    Dim dcUnique As Scripting.Dictionary
    Dim vaData As Variant
    Dim vaSplit As Variant
    Dim i As Long, j As Long

    vaData = Sheet1.Range("$I$12:$I$62").Value
    Set dcUnique = New Scripting.Dictionary

    For i = LBound(vaData, 1) To UBound(vaData, 1)
        vaSplit = Split(vaData(i, 1), ",")
        For j = LBound(vaSplit) To UBound(vaSplit)
            If Not dcUnique.Exists(vaSplit(j)) Then
                dcUnique.Add vaSplit(j), vaSplit(j)
            End If
        Next j
    Next i

    Sheet1.Range("J12").Resize(dcUnique.Count, 1).Value = Application.Transpose(dcUnique.Keys)

End Sub

Thanks. I couldn't get that to work as I wasn't abel to reference MSRuntime. Thanks though. — action jack, Mar 09 '15 at 16:36

score 0 · Answer 2 · answered Mar 09 '15 at 16:39

The code tweak that worked for me was to put the Split at the end.

'Add unique data to new location
For i = 1 To UnqArray.Count
rCell(i, 1) = Split(UnqArray(i), ",")

Next

This then built up an array using data from different ranges and splitting up comma separated ones before outputting only the unique ones.

Creating an array in VBA from spreadsheet data

2 Answers2