VBA Macro that Makes an Array with Data from Column A

Arrays are a very important aspect of many programs. However, they can be a pain to create if you have a huge list of items that you want to make an array with.

I created a VBA macro that makes an array out of a list of items shown in column A and puts the array code in Cell B1. Here is the code:

Sub CreateArrayCode()
Dim lastRow As Long
Dim i As Long
Dim arrayCode As String

' Find the last row with data in Column A
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Start constructing the array code
arrayCode = "Dim boyNames() As Variant" & vbCrLf & "boyNames = Array("

' Loop through each cell in Column A and add the value to the array code
For i = 1 To lastRow
    If i = lastRow Then
        arrayCode = arrayCode & """" & Cells(i, 1).Value & """"
    Else
        arrayCode = arrayCode & """" & Cells(i, 1).Value & """, "
    End If
Next i

' Close the array code
arrayCode = arrayCode & ")"

' Output the array code to the Immediate Window (Ctrl + G to view)
Debug.Print arrayCode

' Optionally, write the array code to a new worksheet
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "ArrayCode"
Cells(1, 1).Value = arrayCode

End Sub

Here are the steps that you will need to take to run this code:

1. You will need to open a new Excel document.
2. You will need to either press the Alt + F11 or click on the Developer Tab and then click “Visual Basic” to open the VBA editor.
3. You will need to click the “Insert” field in the copy menu.
4. Then you will need to click “Module” to insert a new module.
5. Once you have created a new module, you will need to copy and paste the code that we showed above into the module.
6. You will need to either click “Run” or press the F5 key to run the macro.

Of course, these are just the steps that you will need to take to actually run the macro itself. You will need to create a list of elements that are stored in Column A. It is important to make sure that every element of your list is going to be in a separate cell in Column A. Otherwise, you will accidentally combine elements in a single part of your array.

You also need to make sure that you don’t have any important code in Cell B1. If you have any other data in your spreadsheet, it is a good idea to move anything in Column B into another column. Alternatively, you can just adjust the code to put the array code in a cell in another column that is not getting used. However, it is probably a lot easier to just move the contents of Column B to another column.

For this code, I added the following 15 boy names to the list, which are all in column A as shown:

When you click “Run”, the array is created in the first cell of Column B as shown:

We hope that this code helps you if you need to create large arrays for future VBA macros! Take care!