pctechguide.com

  • Home
  • Guides
  • Tutorials
  • Articles
  • Reviews
  • Glossary
  • Contact

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!

Filed Under: Articles

Latest Articles

Writing A Term Paper as a Computer Science Major

There are a lot of different things that you have to deal with as a computer science major. You probably went into the major thinking that you could just write code all day without having to worry about writing term papers. Sadly, this is no the case. Virtually every computer science student will … [Read More...]

How to Keep Your Gadgets Running for a Long Time

Our most valuable source of usefulness is our gadgets. We use them as a source of entertainment when we’re bored. They are also our companions when we travel or go to work. Gadgets are essential to us. That is why it is just right to take care of them. Perhaps, you are already careful with your … [Read More...]

56Kbps Modems

1997 saw the arrival of the 56 Kbit/s modem, despite the absence of any international standard for this speed. The K56Flex group of companies, including 3Com, Ascend, Hayes, Motorola, Lucent and Rockwell, used Rockwell chipsets to achieve the faster speed, while companies like US Robotics used its … [Read More...]

Why Internet Bundles Are the Next Battleground for Gamers

A weak internet connection can turn seamless gaming sessions into frustrating experiences filled with lag, buffering or sudden disconnects. For … [Read More...]

Importance of Inbound Marketing in the Digital Age

A couple of months ago, Zacks reported that Hubspot was starting to make some major changes to its inbound marketing strategy. They talk a lot about … [Read More...]

Damage Control Strategies for Resolving Online PR Crises

Last July, Astrologer faced a major crisis after its CEO went viral at a ColdPlay concert when having an affair. This was just one of the many times a … [Read More...]

AI is Not Killing Computer Jobs Like Doomers Projected

There is no denying the reality that AI technology has played a massive role in disrupting our lives. A growing number of people claim that AI … [Read More...]

Everything You Need to Know About Sourcing Circuit Boards From U.S. Suppliers

In This Article This article includes: Why Source PCBs From the United States?How to Get a Quote From a U.S.-Based PCB ManufacturerThe Top U.S. … [Read More...]

Top Taplio Alternatives in 2025 : Why MagicPost Leads for LinkedIn Posting ?

LinkedIn has become a strong platform for professionals, creators, and businesses to establish authority, grow networks, and elicit engagement. Simple … [Read More...]

Guides

  • Computer Communications
  • Mobile Computing
  • PC Components
  • PC Data Storage
  • PC Input-Output
  • PC Multimedia
  • Processors (CPUs)

Recent Posts

Three Critical Tips for Keeping Your Company Intranet Safe & Secure

Keeping your business safe should be among your top priorities. The fact is, digital threats are more omnipotent than ever. A study from Panda … [Read More...]

Adding Animation and Multimedia to Your PowerPoint Files

Many people are not aware of all of the features that are available with PowerPoint. There is a rich array of media features that you can use to … [Read More...]

IEEE 802.11a

While 802.11a operates in the 2.4GHz ISM (Industrial, Scientific and Medical) band, the 802.11a standard was designed to … [Read More...]

[footer_backtotop]

Copyright © 2026 About | Privacy | Contact Information | Wrtie For Us | Disclaimer | Copyright License | Authors