Learn Macros‎ > ‎VBA Macros‎ > ‎

Store excel range in an array variable

posted Nov 9, 2011, 11:06 PM by Ayush Jain   [ updated Nov 10, 2011, 12:58 AM ]
If you want to store excel range in string array variable , you can use this user define function.
Public Function ReturnArraySel(xlRange As Range) As String()
    Dim strArray() As String
    Dim intctr As Integer
    Dim intCount As Integer
    Dim xlCell As Range
    intctr = 0
    intCount = xlRange.Cells.Count
        ReDim strArray(0 To intCount - 1)
        For Each xlCell In xlRange
                strArray(intctr) = xlCell.Value
                intctr = intctr + 1
    ReturnArraySel = strArray
End Function
You just need to pass the excel range as a parameter to above function and it will return an array string.
Sub StoreArray()
    Dim strArr() As String
    strArr() = ReturnArraySel(ActiveSheet.Range("A1:A50"))
End Sub
Ayush Jain,
Nov 10, 2011, 12:38 AM