Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select a variable range
I am creating a macro that will select a range of cells and use them as a
series in a chart. The range of cells will always be bewteen a cell containing the string "Temperature" and the next blank cell. The location of this range however, is not fixed. I can write the code to find the cell containing the string "Temperature" and move one cell down but cannot work out how to select the range from here to the cell above the next blank cell. All help is greatly appreciated, Amy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select a variable range
If the cell containing the string "Temperature" is the active cell then the
row No of the cell above the next blank cell is rngendrow = ActiveCell.End(xlDown).Row Regards, Stefi €˛AmyHomewood€¯ ezt Ć*rta: I am creating a macro that will select a range of cells and use them as a series in a chart. The range of cells will always be bewteen a cell containing the string "Temperature" and the next blank cell. The location of this range however, is not fixed. I can write the code to find the cell containing the string "Temperature" and move one cell down but cannot work out how to select the range from here to the cell above the next blank cell. All help is greatly appreciated, Amy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select a variable range
Hi Stefi,
Thanks for the quick response but unfortunately rngendrow doesn't give what I'm after. rngendrow gives row 561 which is the last row containing data in the whole sheet, which happens to be D561 not the last cell above the next blank cell of column A which is the column containing the range I am interested in. For this particular set of data the range I want is A202:A241, but I want to be able to select the range bewteen the specified points (between cell containing the string "Temperature" and cell above next blank cell) no matter where they are in the sheet. Amy "Stefi" wrote: If the cell containing the string "Temperature" is the active cell then the row No of the cell above the next blank cell is rngendrow = ActiveCell.End(xlDown).Row Regards, Stefi €˛AmyHomewood€¯ ezt Ć*rta: I am creating a macro that will select a range of cells and use them as a series in a chart. The range of cells will always be bewteen a cell containing the string "Temperature" and the next blank cell. The location of this range however, is not fixed. I can write the code to find the cell containing the string "Temperature" and move one cell down but cannot work out how to select the range from here to the cell above the next blank cell. All help is greatly appreciated, Amy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select a variable range
I suppose that your data structure is something like this:
The cell containing the string "Temperature" is A202 There are Temperature data from A202 to A241 A242 is blank In this case range("A202").End(xlDown).Row returns 241 Post if this is not the case! Stefi €˛AmyHomewood€¯ ezt Ć*rta: Hi Stefi, Thanks for the quick response but unfortunately rngendrow doesn't give what I'm after. rngendrow gives row 561 which is the last row containing data in the whole sheet, which happens to be D561 not the last cell above the next blank cell of column A which is the column containing the range I am interested in. For this particular set of data the range I want is A202:A241, but I want to be able to select the range bewteen the specified points (between cell containing the string "Temperature" and cell above next blank cell) no matter where they are in the sheet. Amy "Stefi" wrote: If the cell containing the string "Temperature" is the active cell then the row No of the cell above the next blank cell is rngendrow = ActiveCell.End(xlDown).Row Regards, Stefi €˛AmyHomewood€¯ ezt Ć*rta: I am creating a macro that will select a range of cells and use them as a series in a chart. The range of cells will always be bewteen a cell containing the string "Temperature" and the next blank cell. The location of this range however, is not fixed. I can write the code to find the cell containing the string "Temperature" and move one cell down but cannot work out how to select the range from here to the cell above the next blank cell. All help is greatly appreciated, Amy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select a variable range
The data is as you said but columns C and D also have data which runs down to
row 561. The code you gave in the last post is coming up with an error saying incorrect use of the row command. I have however managed to perform the task I was after. Thanks again for your responses Amy "Stefi" wrote: I suppose that your data structure is something like this: The cell containing the string "Temperature" is A202 There are Temperature data from A202 to A241 A242 is blank In this case range("A202").End(xlDown).Row returns 241 Post if this is not the case! Stefi €˛AmyHomewood€¯ ezt Ć*rta: Hi Stefi, Thanks for the quick response but unfortunately rngendrow doesn't give what I'm after. rngendrow gives row 561 which is the last row containing data in the whole sheet, which happens to be D561 not the last cell above the next blank cell of column A which is the column containing the range I am interested in. For this particular set of data the range I want is A202:A241, but I want to be able to select the range bewteen the specified points (between cell containing the string "Temperature" and cell above next blank cell) no matter where they are in the sheet. Amy "Stefi" wrote: If the cell containing the string "Temperature" is the active cell then the row No of the cell above the next blank cell is rngendrow = ActiveCell.End(xlDown).Row Regards, Stefi €˛AmyHomewood€¯ ezt Ć*rta: I am creating a macro that will select a range of cells and use them as a series in a chart. The range of cells will always be bewteen a cell containing the string "Temperature" and the next blank cell. The location of this range however, is not fixed. I can write the code to find the cell containing the string "Temperature" and move one cell down but cannot work out how to select the range from here to the cell above the next blank cell. All help is greatly appreciated, Amy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select a variable range
Yes, because the whola command is
rngendrow = range("A202").End(xlDown).Row If you want to apply it to column C then rngendrow = range("C202").End(xlDown).Row Stefi €˛AmyHomewood€¯ ezt Ć*rta: The data is as you said but columns C and D also have data which runs down to row 561. The code you gave in the last post is coming up with an error saying incorrect use of the row command. I have however managed to perform the task I was after. Thanks again for your responses Amy "Stefi" wrote: I suppose that your data structure is something like this: The cell containing the string "Temperature" is A202 There are Temperature data from A202 to A241 A242 is blank In this case range("A202").End(xlDown).Row returns 241 Post if this is not the case! Stefi €˛AmyHomewood€¯ ezt Ć*rta: Hi Stefi, Thanks for the quick response but unfortunately rngendrow doesn't give what I'm after. rngendrow gives row 561 which is the last row containing data in the whole sheet, which happens to be D561 not the last cell above the next blank cell of column A which is the column containing the range I am interested in. For this particular set of data the range I want is A202:A241, but I want to be able to select the range bewteen the specified points (between cell containing the string "Temperature" and cell above next blank cell) no matter where they are in the sheet. Amy "Stefi" wrote: If the cell containing the string "Temperature" is the active cell then the row No of the cell above the next blank cell is rngendrow = ActiveCell.End(xlDown).Row Regards, Stefi €˛AmyHomewood€¯ ezt Ć*rta: I am creating a macro that will select a range of cells and use them as a series in a chart. The range of cells will always be bewteen a cell containing the string "Temperature" and the next blank cell. The location of this range however, is not fixed. I can write the code to find the cell containing the string "Temperature" and move one cell down but cannot work out how to select the range from here to the cell above the next blank cell. All help is greatly appreciated, Amy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to select a variable range | Excel Programming | |||
Select variable range | Excel Programming | |||
Select a variable range | Excel Programming | |||
Use a Variable to select a range | Excel Discussion (Misc queries) | |||
select a variable range | Excel Programming |