Fill array from “Selection”

Topic

New Reply

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

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

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:

Reply | Quote

 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.

Reply | Quote

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??

Reply | Quote

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

Reply | Quote