Many a times while doing coding in excel, we need all the values stored in a Cell Range in an Array format. So that during any point of time we can access the value from the array, rather going in Excel Cell and reading from there.

Thinking that in mind, I just created a Function (UDF – User Defined Function) which will accept Cell Range as Input and Return a String Array having all the values in that cell Range.

Step 1. Copy and Paste the below code in Excel Macro Module

Public Function GetArray(xlRange As Range) As String()
    Dim strArray() As String
    Dim iCounter As Integer
    Dim intCount As Integer
    Dim xlCell As Range
    iCounter = 0
    intCount = xlRange.Cells.Count
        ReDim strArray(0 To intCount - 1)
        For Each xlCell In xlRange
                strArray(iCounter) = xlCell.Value
                iCounter = iCounter + 1
    GetArray = strArray
End Function

Step 2. You can use the above Function anywhere in the Excel macro and it will return the String Array.

Sub GetArray()
    Dim strArr() As String
    strArr() = GetArray(ActiveSheet.Range("A1:A5"))
End Sub

I am storing All the values in a Single Dimentional Array. Therefore If you are passing Range of Single Row or a Single Column then all the Values will be Stored as a single array.

For Example: 1. If the Range is “A1:A5” and Array is strArr then Array will be defined as below:

Excel Range To String Array

Excel Range To String Array

For Example: 2. If the Range is “A1:B5” and Array is strArr then Array will be defined as below:

Excel Range to String Array

Excel Range to String Array

If you want the workbook with this Function Mail Me

To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial


Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!