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