Adding strings from range into an array

Question

Now this could sound very silly as it looks very simple but I am not able to understand the way how to declare a single dimension array that can take strings from a range as values. Here is what I am trying to do.

Sub myArr()
Dim V(1 To 2) As String
V(1) = "Hello"
V(2) = "World"
'V = Range("A1:A2").Value 'What is wrong in this syntax?
End Sub

I have two strings "Hello" & "World" in range A1:A2. I searched for it and saw some articles where loops were written to get all the string values in the range into an array of string type. How can I get this into an array in a single line syntax. Is there a way to do it? Please help.

EDIT : for better explanation

I am looking for a 1D array and wanted to check if there is a single line code that can put range values in the array which should be of type string without having to loop its elements. I have updated my question. Kindly have a look at it.

If I loop through the elements, I would get something like this

But I am looking for string type values like below with a one liner code

Range("A1:A2").Value will return a 2 dimensional array. What are you looking for ? — shahkalpesh
– shahkalpesh, Commented Jul 7, 2021 at 11:38
The simplest way looks to be Dim v, then V = Range("A1:A2").Value will return a 2D array and you can use its elements as Debug.print V(1,1), V(2, 1). Is it a need to be 1D? — FaneDuru
– FaneDuru, Commented Jul 7, 2021 at 12:08
And the syntax is bad only against the array declaration... It should be declared as Variant. As I tried showing in my previous comment. — FaneDuru
– FaneDuru, Commented Jul 7, 2021 at 12:18

CDP1802 · Accepted Answer · 2021-07-07 11:57:51Z

1

If the range is a column then use Transpose, providing there less than 65,537 rows.

Sub myArr()
    Dim V As Variant, i As Long
    V = Application.Transpose(Range("A1:A2").Value)
    For i = LBound(V) To UBound(V)
        Debug.Print i, V(i)
    Next
End Sub

edited Jul 7, 2021 at 11:57

answered Jul 7, 2021 at 11:50

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

4 Comments

Kawalpreet Kaur Over a year ago

Thanks for your reply but i was looking for a one-line. I have updated my question for better understanding of what I am trying to achieve.

CDP1802 Over a year ago

V = Application.Transpose(Range("A1:A2").Value) is one line and V is a ID array.

Kawalpreet Kaur Over a year ago

I tried this already. if you look at the VBE Locals windows and the screenshot I provided on top, it shows type is Variant/String. I wanted only string. I just now got my answer with this one line where the type shows String V = Filter(Application.Index(Range("A1:A2").Value, [{1,2}]), False, False) but the problem here is, I cannot keep on adding {1,2,3,4,5} for a larger data.

CDP1802 Over a year ago

@kawal V = Filter(Application.Transpose(Range("A1:A10").Value), "")

Collectives™ on Stack Overflow

Adding strings from range into an array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related