Dynamic Cell Selection

[Rwem]

I'm looking for a way to dynamically select a range of cells.

I have a spreadsheet in which the number of rows and the number of columns
changes from time to time. By using the Count Worsheet function, I can
determine the number of rows and the number of columns is selected from an
array of values. I want to be able to select and copy a subset of the total
number of cells contained in the Worksheet and paste them on a separate
Worksheet. I will always know the primary reference cell.

For example, this week the Worksheet contains 150 rows of information. I
want to copy and paste 50 rows and 5 columns of the information to a
separate worksheet. Assuming a reference cell of $A$1, the 'standard' method
for doing this when the select range is known is: Range("A1:E50").Select.

However, since the number of rows (and columns) changes, I need to
dynamically set the selected range.

It seems that the OFFSET property might work, but I've only been successful
at selecting a single cell rather than a range of cells.

Anyone have any suggestions.

Ron W

 

[Don Guillett]

You could create a define range.
Insert>name>define>name it whatever>in the refers to
=offset($a$1,0,0,counta($a:$a),counta($1:$1))
This assumes no breaks in the data column or data row.
It there are breaks you would have to do it differently.

 

[Tom Ogilvy]

NumRows = 50
NumColumns = 5
Range("A1").Resize(NumRows,NumColumns).Copy

 

[Norman Jones]

Hi Ron.

Possibly you are looking for the Resize property. For example, from the
intermediate window:

?Range("A1").Offset(2,2).Resize(10, 5).Address

$C$3:$G$12