Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I need to fill a selected range into an array and are aware of the "tedious" way of doing it.
    (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 possible to do somthing similar in a simple way without a big hazzle??

    TIA
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Rubber Duck,

    You might want to check out this article (you'll need to scroll down a bit) for a more robust solution, although in essence it's the same as yours.
    Using Ranges
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A really informative article.

    Thanks for the input RG - I appreciate it.

    Despite the clarity of the article, I have an example below where I just don't understand what's going on. Maybe you can explain it to me...?

    Got a small loop counting down and an array filled with data:

    For d = UBound(varGetArrayAll, 1) To 1 Step -1
    Range(sRangeAddress).Select
    Selection.Rows(d).Select
    Debug.Print Selection.Rows(d).Address
    next d

    I want to format a range, say "B6:F19" (named: "sRangeAddress"). Within the range, different rows have different formats. (eg. B6 is title, B7 is heading, B8-B17 is data. B18 a source and B19 a comment. All number of rows can vary)

    "Selection.Rows(d).Select" selects the right row.
    But "Selection.Rows(d).Address" provides an address offset with d to the range.

    What I don't understand is how the same "question" -
    Selection.Rows(d) - can result in two different addresses...

    Any clues??

    Bests


    PS:
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    *Selection.Rows(d).Select
    *Debug.Print Selection.Rows(d).Address


    with the Select statement you have altered the selection that is referred to in the following line. Note: it is almost never necessary to actually select anything in code to manipulate it.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory,

    Normally I don't do it either, but problem here is that .activate gives me the current selection - which is the entire range (B6:F19) and not just a single row.

    Therefore I decided to try the .select instead. Problem is that it creates another "problem".

    Any idea to how I might be able to get around this??
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You don't need to Activate either:

    Code:
    For d = UBound(varGetArrayAll, 1) To 1 Step -1
    Debug.Print Range(sRangeAddress).Rows(d).Address
    next d

    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •