VBA: Add single value to single column array

Question

I do not know the size of the array.
The array is created from a column L on sheet "INPUT_MASTERDATA".

This is what I have so far:

With Worksheets("INPUT_MASTERDATA")
    arrInputUniqueItems = .Range("L2", .Range("L" & Rows.Count).End(xlUp))
End With

I would like to include the value "x" in the above array.

Sample data from Range L on Worksheet "INPUT_MASTERDATA"

These all goes into the array just fine. Now I would like to add the value "x". So the array afterwards looks like this:

Any ideas or help is highly appreciated! Thank you in advance.

score 1 · Accepted Answer · 2014-11-04 10:06:09Z

1

If you are going to be dynamically adding items to your collection you should consider a better suitable data type like Collection. Array's are not supposed by resized after their dimensions are specified.

So... consider creating an array from range in one go, then loading that into a Collection and then you can add more items to the collection. The reason you want to use the array is to load the entire range into memory is going to be faster than iterating a Range and adding each cell to the collection directly.

Sub Main()

    [L1] = "header"
    [L2] = "R83711850"
    [L3] = "1210221340"
    [L4] = "1210223342"
    [L5] = "R83711181"
    [L6] = "R83711931"

    Dim lastRow As Long
    lastRow = Range("L" & Rows.Count).End(xlUp).Row

    Dim v As Variant
    Dim c As New Collection

    Dim arr As Variant
    arr = Range("L2:L" & lastRow).Value

    For Each v In arr
        c.Add v
    Next

    ' then if you ever need to add more just add it to the collection
    c.Add "new value"

    ' print to confirm
    For Each v In c
        Debug.Print v
    Next

End Sub

prints

in the Immediate Window CTRL+G

edited Nov 4, 2014 at 10:06

answered Nov 4, 2014 at 9:46

user2140173

Sign up to request clarification or add additional context in comments.

2 Comments

Philip Over a year ago

Thank you, that helps a lot though I get a runtime error 13. I was trying to follow this solution stackoverflow.com/questions/7739510/…. But I want to add the value x to the collection so that these rows will not be removed. Any idea on that? I think my error has to do with the use of cstr

user2140173 Over a year ago

I believe this answer answers your original question. If you have a new question please consider asking a new one with your current code as modifying this question would invalidate the answer.

brettdj · Accepted Answer · 2015-03-13 13:31:29Z

You can do this easily enough without resorting to further loops and/or other methods.

Simply use a range 1 cell longer than the end of the range of interest, or
Use ReDim on a 1D array (of course you can and should resize arrays)

code 1

Sub Method1_2D()
Dim arrInputUniqueItems
With Worksheets("INPUT_MASTERDATA")
    arrInputUniqueItems = .Range("L2", .Range("L" & Rows.Count).End(xlUp).Offset(1, 0))
    arrInputUniqueItems(UBound(arrInputUniqueItems), 1) = "X"
End With
End Sub

code 2

Sub Method2_1D()
Dim arrInputUniqueItems
With Worksheets("INPUT_MASTERDATA")
    arrInputUniqueItems = Application.Transpose(.Range("L2", .Range("L" & Rows.Count).End(xlUp)))
    ReDim Preserve arrInputUniqueItems(UBound(arrInputUniqueItems))
    arrInputUniqueItems(UBound(arrInputUniqueItems)) = "X"
End With
End Sub

Collectives™ on Stack Overflow

VBA: Add single value to single column array

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related