Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Cell Selection
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Cell Selection
NumRows = 50
NumColumns = 5 Range("A1").Resize(NumRows,NumColumns).Copy -- Regards, Tom Ogilvy "Rwem" wrote in message news:kz7Pc.358$73.144@lakeread04... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Cell Selection
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 --- Regards, Norman "Rwem" wrote in message news:kz7Pc.358$73.144@lakeread04... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range Selection Using VBA | Excel Discussion (Misc queries) | |||
SUBTOTAL - dynamic selection | Excel Worksheet Functions | |||
dynamic selection of cells | New Users to Excel | |||
Macro - Dynamic cell selection | Excel Discussion (Misc queries) | |||
Dynamic Row Selection | Excel Programming |