Make and array from names on a sheet

jim101

Board Regular
Joined
Mar 22, 2005
Messages
110
I have names listed in sheet1 A1:A11

Tom
Scott
Paul
Joe
Bruce
Joy
Daniel
Ruth
Tim
Dan
Mike

Is there any way to make them into and array like this, without having to type the names again? Or can you use a sheet reference in an array? Real data has more names

MyNames = Array("Tom", "Scott", "Paul", "Joe", _
"Bruce", "Joy", "Daniel", "Ruth", "Tim", _
"Dan", "Mike")
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Probably not the best way to go about doing this, but should work:

Code:
Dim MyNames As Variant, c As Range

'loop through the range and add names as a string
' (Ex.: "Tom","Scott","Paul", etc.)
For Each c In Range("A1:A11")
    If MyNames = "" Then
        MyNames = """" & c.Value & """"
    Else
        MyNames = MyNames & ",""" & c.Value & """"
    End If
Next c

' split list into array, delimited by commas
MyNames = Split(MyNames, ",")

Note: I have no idea what the data limitations are. Depending on how many names you have, you could run into problems with the array not having enough "space." Also, this does not take duplicate names into account--just adds each name from the list.
 
Upvote 0
Kristy, not sure what the MyNames = Split(MyNames, ",") did, and not sure if this is how you intended it to work but I wanted to output the array so I could copy it and put it in some code without typing the names again, I got what I wanted with Debug.Print "MyNames = Array(" & MyNames & ")"
Thanks


Code:
Sub Make_Array()
Dim MyNames As Variant, c As Range

'loop through the range and add names as a string
' (Ex.: "Tom","Scott","Paul", etc.)
For Each c In Range("A1:A11")
    If MyNames = "" Then
        MyNames = """" & c.Value & """"
    Else
        MyNames = MyNames & ",""" & c.Value & """"
    End If
Next c
Debug.Print "MyNames = Array(" & MyNames & ")"

' split list into array, delimited by commas
'MyNames = Split(MyNames, ",")
'or to put in in the sheet
Range("B1") = "MyNames = Array(" & MyNames & ")"
End Sub
 
Upvote 0
Try this.
Code:
Dim arrNames
    
    arrNames = Range("A1:A11")
    
    arrNames = Application.Transpose(arrNames)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top