BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Load Array from Sheet Range

The post starts with a bad news, I had a severe car accident causing 6 fractures in 5 ribs around 10 days before. I express my sincere THANKS to God, Family and my well wishers, everything is fine now... though I'm on bed rest but can't resist myself posting on the blog.

This post is about a VBA trick can be considered as short tip for my fellow readers... If you frequently use arrays and wants to load the values dynamically from a particular range then this VBA macro will be very handy to you.

Sub Load_Array_Dynamically_From_Range()

    Dim arrRng()
    Dim Rng As Range

    x = 0

    For Each Rng In Range("A1:A5")
        ReDim Preserve arrRng(x)
        arrRng(x) = Rng.Value
        x = x + 1
    Next Rng

    For Each Item In arrRng
        Debug.Print Item

End Sub

Anonymous said...



EEGraham said...

I find the following much easier and much much faster:

Sub LoadArray_Test()

Dim vArray As Variant
Dim rRange As Range

Set rRange = Range("A1:C5")

LoadArray vArray, rRange

For Each Item In vArray
Debug.Print Item

End Sub

Sub LoadArray(ByRef vArray As Variant, ByVal rRange As Range)

vArray = rRange.Value

End Sub

jimmyguan said...

Great macro. Definitely keeping this one in my back pocket.