Make and array from names on a sheet

jim101

Board Regular
Joined
Mar 22, 2005
Messages
108
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")
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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.
 

jim101

Board Regular
Joined
Mar 22, 2005
Messages
108
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Dim arrNames
    
    arrNames = Range("A1:A11")
    
    arrNames = Application.Transpose(arrNames)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,919
Messages
5,545,028
Members
410,647
Latest member
bernardazar
Top