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 |
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 |
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 |
All times are GMT +1. The time now is 08:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com