Getting values from a variable sized range into an array
 

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

Getting values from a variable sized range into an array
 

Hi Ken,

Try:

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


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Getting values from a variable sized range into an array
 

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)


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Getting values from a variable sized range into an array
 

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

"Norman Jones" wrote in message
...
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)


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Getting values from a variable sized range into an array
 

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" wrote in message
...
Hi Ken,

Try:

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


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Getting values from a variable sized range into an array
 

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



"Ken Loomis" wrote in message
...
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" wrote in message
...
Hi Ken,

Try:

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


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Getting values from a variable sized range into an array
 

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.

---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

"Norman Jones" wrote in message
...
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)


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Getting values from a variable sized range into an array
 

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" wrote in message
...
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



"Ken Loomis" wrote in message
...
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" wrote in message
...
Hi Ken,

Try:

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


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Getting values from a variable sized range into an array
 

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.

---
Regards,
Norman



"Ken Loomis" wrote in message
...
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" wrote in message
...
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



"Ken Loomis" wrote in message
...
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" wrote in message
...
Hi Ken,

Try:

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


---
Regards,
Norman



"Ken Loomis" wrote in message
...
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