XL5 VBA problem: Reading values from a range into an array

XL5 VBA problem: Reading values from a range into an array

Post by David Nickel » Fri, 01 Dec 1995 04:00:00



Terry -
I have used a very simple method to count the number rows of in a named
range.  Try the following code:

    rowCount = ActiveSheet.Range("TestRange").Rows.Count

If you leave out the "Rows", then the resulting command will count the
number of cells in the entire range.

If the range is not named you can dynamically determine the boundaries
of the desired range.  To do that you can use code something like the
following:

With Activesheet
 upperLeftCorner = .Range("TopCorner")
 lowerLeftCorner = .Range(upperLeftCorner).End(xlDown).Address
 lowerRightCorner = .Range(lowerLeftCorner).End(xlToRight).Address
end with

The addresses can then be used to define the range in question.

The above code comes with some restrictions.  1) If your range only has
one cell, .End(xlDown).Address will give the address of the very last
cell in that column.  2) As you might expect, if the range contains only
one column, .End(xlRight).Address gives the address of the last cell in
that row (the far right column of the spreadsheet).

Hope this is helpful.

Dave