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
    Next

End Sub




Comments

Anonymous said…
GET WELL SOON :) MISS YOUR EXCEL INTELLIGENCE..


REGARDS
YOUR FAN
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
Next

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.