none
Excel VBA: Populate (fill) an array directly from a "Selection" or range

    Question

  • Hi

    (Env: Win XP - SP3, O2003 pro - all UK versions)

    I need to fill a selected range into an array and are aware of the "tedious" way of doing it. :wacko:
    (If possible I would like not to have to investigate range address, sheet name etc.)

    So question is: Is it possible to fill an array directly based on "selection".

    Something like:

    varGetArray= Selection.range

    ...and obviously the above doesn't work, but is it somehow possible to do something similar in a single line or two?? :clapping:

    TIA

    Wednesday, July 14, 2010 10:40 AM

Answers

  • Found an answer to my question...

    Public Function Array_FillFromSelection()
        Dim c
        Dim d
        Dim varGetArrayAll As Variant
       
        varGetArrayAll = ActiveCell.CurrentRegion.Value
        For c = 1 To UBound(varGetArrayAll)
            For d = 1 To UBound(varGetArrayAll, 2)
                Debug.Print varGetArrayAll(c, d)
            Next d
        Next c
    End Function

    • Marked as answer by Bessie Zhao Tuesday, July 20, 2010 10:11 AM
    Wednesday, July 14, 2010 12:07 PM

All replies

  • Found an answer to my question...

    Public Function Array_FillFromSelection()
        Dim c
        Dim d
        Dim varGetArrayAll As Variant
       
        varGetArrayAll = ActiveCell.CurrentRegion.Value
        For c = 1 To UBound(varGetArrayAll)
            For d = 1 To UBound(varGetArrayAll, 2)
                Debug.Print varGetArrayAll(c, d)
            Next d
        Next c
    End Function

    • Marked as answer by Bessie Zhao Tuesday, July 20, 2010 10:11 AM
    Wednesday, July 14, 2010 12:07 PM
  • Your question was how to fill an array from the selection, to which the
    answer is simply ..
     
    varGetArrayAll = Selection
     
    .. but you seem to have found an equally simple solution to a different, if
    related, question.
     
    --
    Enjoy,
    Tony
     
    www.WordArticles.com
     
    "Rubberduckone" wrote in message
    news:18fc54e4-37e9-4ea7-a465-496ed7d27150...
    > Found an answer to my question...
    >
    > Public Function Array_FillFromSelection()
    > Dim c
    > Dim d
    > Dim varGetArrayAll As Variant
    >
    > varGetArrayAll = ActiveCell.CurrentRegion.Value
    > For c = 1 To UBound(varGetArrayAll)
    > For d = 1 To UBound(varGetArrayAll, 2)
    > Debug.Print varGetArrayAll(c, d)
    > Next d
    > Next c
    > End Function
    >
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    Saturday, July 17, 2010 11:33 AM