Range to Array

[galore]

Hi,

I can not find how to put the strings i have in a range (a1:a10) into an array of string that I can user later on with a loop.

PD: The range may have 10, 5 or 200 strings (cells) and the array (Sub) should recognize the number of items.

Thanks.

A1: John
a2: Mary
etc..

How can I do this dynamically:

Public Sub SingleDimension()

' Define a variant to hold individual strings.
Dim IndividualString As Variant

' Define the array of strings.
Dim StringArray(5) As String

' Fill each array element with information.
StringArray(0) = "This"
StringArray(1) = "Is"
StringArray(2) = "An"
StringArray(3) = "Array"
StringArray(4) = "Of"
StringArray(5) = "Strings"

' Use the For Each...Next statement to get each array
' element and place it in a string.
For Each IndividualString In StringArray

' Create a single output string with the array
' array elements.
Output = Output + IndividualString + " "
Next

' Display the result.
MsgBox Trim(Output), _
vbInformation Or vbOKOnly, _
"Array Content"
End Sub


Thanks

 

[pgc01]

Hi

Assuming the range is contiguous, starts in A1, down, try:

Sub RngArr()
Dim r As Range
Dim arr As Variant
 
Set r = Range("A1", Range("A1").End(xlDown))
 
' get the values of the range into an array
arr = Application.Transpose(r)
 
' display the values of the array
MsgBox Join(arr, ", ")
End Sub

 

[galore]

It works great. Thank very much.

Is this always like this? or is a membership needed?

Thanks again.

 

[Richard Schollar]

Is this always like this? or is a membership needed?

It's always like this

Signing up is free, posting is free - you can even get humour thrown in in the cost (try reading any of Greg Truby's posts!)

We like it that way

 

[snowblizz]

It's always like this

Signing up is free, posting is free - you can even get humour thrown in in the cost (try reading any of Greg Truby's posts!)

We like it that way

Though sometimes you do wonder if it was possible to pay to get rid of the smartmouth comments.

 

[galore]

I like a little sense of humor too