Getting values from a variable sized range into an array

[Ken Loomis]

I need to get the values from a range of cells into an array. The range will
always start in the same cell on the sheet where it resides and it will
always be 3 cells wide. But it can grow in length.

Right now I get the values into the array with this statement:

Dim ReplaceArray As Variant

ReplaceArray = Worksheets(2).Range("a5:c6")

and that works fine as long as the range of cells containing the data is
static. Right now that works ok since I can manually change things as
needed, but I'd like to automate this and give the users the ability to add
to that list.

If I need to, I can increment a cell value on worksheet 2 that would be the
number of rows in the range, but I don't even know how to refer to the range
that way. and, there must be a better, more dynamic (and elegant) way to do
this. There is nothing on worksheet 2 below or to the right of the last cell
in the range, i.e. "C6" in the example above.

Thanks for any help on this.

Ken Loomis

 

[Norman Jones]

Hi Ken,

Try:

Dim Rng as Range
Set Rng = Worksheets(2).Range(2A5").CurrentRegion

 

[Norman Jones]

Hi Ken,

Longer, but safer, in that it relies only on the C column being empty below
your range, is the following:

Dim Rng1 As Range, Rng2 As Range
Dim MyRng As Range
With Worksheets(2)

Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.Count, "C").End(xlUp)
End With
Set MyRng = Range(Rng1, Rng2)

 

[Ken Loomis]

Not sure I used this right, but here is what I coded from what I understood
you to say:

Dim Rng As Range

Set Rng = Worksheets(2).Range("A5").CurrentRegion
ReplaceArray = Rng

However, that filled the array with everything from the worksheet, including
rows 1 thru 4, which are not part of the array data.
Cells A1 thru A4 are intructions about the information below. The actual
array data is in the range A5:C6. But apparently the code I used above set
the Rng to A1:C6.

What did I do wrong?

I am really new at this and have only been at it for a couple of days.

Ken Loomis

 

[Ken Loomis]

Thanks, Norman.

This one works fine.

I have to admit though that I do not really understand it.

Can someone recommend a reference where I could learn more about this range
stuff? I read a Dummies book on this over the weekend and it got me started.
I am pretty pleased with the application I wrote to automatically combine,
clean, strip duplicates and format 4 separate database queries files of
varying formats into a single color coded report, but I would like to
understand this range stuff better. Pretty cool that something that used to
take someone 2 hours to do manually is now done in under a minute.

Thanks again for you help.

Ken Loomis

 

[Norman Jones]

Hi Ken,

This one works fine.

I have to admit though that I do not really understand it.

ActiveCell.CurrentRegion is the active cell extended in all directions until
it is bounded by a rectangle comprising blank cells or the sheet border. The
anchor cell here is the active cell but it can be any cell or range. In your
case we used the range start cell A5 as the anchor cell; it was possible to
use the CurrentRegion property because the rows/columns surrounding the area
of interest were either blank or sheet borders.

As for suitable books, if you do a Google search for "book" you will find
many repeated recommendations and, if the author's initials are JW and the
title includes the word "Power", go with it! Later, as your appetite and
proficiency increase, look for John Green, Stephen Bullen, Rob Bovey,
Robert Rosenberg's book: Excel 2002 VBA Programmers Reference.


Regards,
Norman

 

[Norman Jones]

Hi Ken,

However, that filled the array with everything from the worksheet,
including
rows 1 thru 4, which are not part of the array data.
Cells A1 thru A4 are intructions about the information below. The actual
array data is in the range A5:C6. But apparently the code I used above set
the Rng to A1:C6.

What did I do wrong?

In a response to your earlier post (written before I read this) I tried to
explain the CurrentRegion property. In this case, it fails because the cells
immediately above the range of interest are not blank, Essentially, in order
successfully to use the property, the required range needs to be
defined/bounded by a blank border.

That was the reason for my second ("longer but safer") suggestion , which
only requires that column C be blank below the range of interest. So try the
latter suggestion.

 

[Ken Loomis]

Thanks, Norman.

I have several situation where "ActiveCell.CurrentRegion" would be a much
better solution than what I used.

I 'Dummies' book read was by JW. And though it did get me going and it was
for beginners, parts of it left me very confused. I have been programming
since, well, let's just say that the computer I first programmed would fill
a house and back then we thought a 64 instruction calculator the size of a
desk was cool.

It's seems to be the OOP part of VBA and the ranges that mystify me.

Does his 'Power' book go more in depth for someone that has programmed in
about 20 different languages or should I just go for the other one you
suggested?

thanks,
Ken Loomis


Ken Loomis

 

[Norman Jones]

Hi Ken,

I would have no hesitation in recommending the Excel 200x Power Programming
with VBA book. Conversely, given your programming pedigree, I would probably
not suggest the Dummies book,

Vis-a-vis the John Green book, I believe (but may be mistaken) that the 2003
version did not enjoy the collaboration of John Green or Srephen Bullen.
From a personal perspective, that would tend to tip the scales in favour of
the 2002 ediition.