Macro to select a variable range

[AmyHomewood]

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

 

[Stefi]

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:

 

[AmyHomewood]

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]

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:

 

[AmyHomewood]

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]

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: