For keystrokes, I want my macro to go to a fixed cell location, say cell
N18, then while holding the shift key down, then do an end-down, then move
the bottom up by two rows (same thing as two cells since it's a single
column), then release the shift key. In this way, I select a
variable-length block of cells that is the whole contiguous block except for
the bottom two cells which I always want to ignore.

I try recording these keystrokes in a macro with "use relative references"
set to either of the two possible ways, but either way, the macro records a
fixed set of cells, not the variable length block I need in general. Why
doesn't it work? And, if recording can't do this, please give me the code
that works - I get a little confused on the syntax.

Here are the two ways it comes out (for the particular none-empty cells I
have right now), neither of which is a variable length depending upon where
the bottom of the list happens to be:

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A45").Select


or

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
Range("N18:N62").Select

Thanks!
Dean

Hello

Maybe this tip can help...

http://www.j-walk.com/ss/excel/tips/tip10.htm

It's on the site of John Walkenbach, an Excel goeroe!

greetz

"Dean" schreef in bericht
...
For keystrokes, I want my macro to go to a fixed cell location, say cell
N18, then while holding the shift key down, then do an end-down, then move
the bottom up by two rows (same thing as two cells since it's a single
column), then release the shift key. In this way, I select a
variable-length block of cells that is the whole contiguous block except
for the bottom two cells which I always want to ignore.

I try recording these keystrokes in a macro with "use relative references"
set to either of the two possible ways, but either way, the macro records
a fixed set of cells, not the variable length block I need in general.
Why doesn't it work? And, if recording can't do this, please give me the
code that works - I get a little confused on the syntax.

Here are the two ways it comes out (for the particular none-empty cells I
have right now), neither of which is a variable length depending upon
where the bottom of the list happens to be:

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A45").Select


or

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
Range("N18:N62").Select

Thanks!
Dean

He doesn't exactly tell us why the recording doesn't work, but this will do
just fine, I suppose. But will this work if I then try to go back up by two
rows?

Thanks!
Dean

"Frederik" wrote in message
...
Hello

Maybe this tip can help...

http://www.j-walk.com/ss/excel/tips/tip10.htm

It's on the site of John Walkenbach, an Excel goeroe!

greetz

"Dean" schreef in bericht
...
For keystrokes, I want my macro to go to a fixed cell location, say cell
N18, then while holding the shift key down, then do an end-down, then
move the bottom up by two rows (same thing as two cells since it's a
single column), then release the shift key. In this way, I select a
variable-length block of cells that is the whole contiguous block except
for the bottom two cells which I always want to ignore.

I try recording these keystrokes in a macro with "use relative
references" set to either of the two possible ways, but either way, the
macro records a fixed set of cells, not the variable length block I need
in general. Why doesn't it work? And, if recording can't do this, please
give me the code that works - I get a little confused on the syntax.

Here are the two ways it comes out (for the particular none-empty cells I
have right now), neither of which is a variable length depending upon
where the bottom of the list happens to be:

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A45").Select


or

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
Range("N18:N62").Select

Thanks!
Dean

Range(Range("N18"), Range("N18").End(xlDown).Offset(-2, 0)).Select


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Dean" wrote in message
...
He doesn't exactly tell us why the recording doesn't work, but this will
do just fine, I suppose. But will this work if I then try to go back up
by two rows?

Thanks!
Dean

"Frederik" wrote in message
...
Hello

Maybe this tip can help...

http://www.j-walk.com/ss/excel/tips/tip10.htm

It's on the site of John Walkenbach, an Excel goeroe!

greetz

"Dean" schreef in bericht
...
For keystrokes, I want my macro to go to a fixed cell location, say cell
N18, then while holding the shift key down, then do an end-down, then
move the bottom up by two rows (same thing as two cells since it's a
single column), then release the shift key. In this way, I select a
variable-length block of cells that is the whole contiguous block except
for the bottom two cells which I always want to ignore.

I try recording these keystrokes in a macro with "use relative
references" set to either of the two possible ways, but either way, the
macro records a fixed set of cells, not the variable length block I need
in general. Why doesn't it work? And, if recording can't do this,
please give me the code that works - I get a little confused on the
syntax.

Here are the two ways it comes out (for the particular none-empty cells
I have right now), neither of which is a variable length depending upon
where the bottom of the list happens to be:

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A45").Select


or

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
Range("N18:N62").Select

Thanks!
Dean

Thanks, Bob.

"Bob Phillips" wrote in message
...
Range(Range("N18"), Range("N18").End(xlDown).Offset(-2, 0)).Select


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Dean" wrote in message
...
He doesn't exactly tell us why the recording doesn't work, but this will
do just fine, I suppose. But will this work if I then try to go back up
by two rows?

Thanks!
Dean

"Frederik" wrote in message
...
Hello

Maybe this tip can help...

http://www.j-walk.com/ss/excel/tips/tip10.htm

It's on the site of John Walkenbach, an Excel goeroe!

greetz

"Dean" schreef in bericht
...
For keystrokes, I want my macro to go to a fixed cell location, say
cell N18, then while holding the shift key down, then do an end-down,
then move the bottom up by two rows (same thing as two cells since it's
a single column), then release the shift key. In this way, I select a
variable-length block of cells that is the whole contiguous block
except for the bottom two cells which I always want to ignore.

I try recording these keystrokes in a macro with "use relative
references" set to either of the two possible ways, but either way, the
macro records a fixed set of cells, not the variable length block I
need in general. Why doesn't it work? And, if recording can't do this,
please give me the code that works - I get a little confused on the
syntax.

Here are the two ways it comes out (for the particular none-empty cells
I have right now), neither of which is a variable length depending upon
where the bottom of the list happens to be:

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A45").Select


or

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
Range("N18:N62").Select

Thanks!
Dean

For the following follow-up question, ignore the two row offset which
applies in a different worksheet. Occasionally, (in the non offset case),
there is no other entry below the first cell in question, in which case the
end-down brings us to the bottom of the worksheet, row 65536 or whatever.
Is there are a way to rewrite your command to tell the macro not to do the
end down in that event, but just select the single cell? BTW, if the cell
below is empty, all of the cells below will be also, so there is no risk of
missing something.

Thanks!
Dean

"Bob Phillips" wrote in message
...
Range(Range("N18"), Range("N18").End(xlDown).Offset(-2, 0)).Select


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Dean" wrote in message
...
He doesn't exactly tell us why the recording doesn't work, but this will
do just fine, I suppose. But will this work if I then try to go back up
by two rows?

Thanks!
Dean

"Frederik" wrote in message
...
Hello

Maybe this tip can help...

http://www.j-walk.com/ss/excel/tips/tip10.htm

It's on the site of John Walkenbach, an Excel goeroe!

greetz

"Dean" schreef in bericht
...
For keystrokes, I want my macro to go to a fixed cell location, say
cell N18, then while holding the shift key down, then do an end-down,
then move the bottom up by two rows (same thing as two cells since it's
a single column), then release the shift key. In this way, I select a
variable-length block of cells that is the whole contiguous block
except for the bottom two cells which I always want to ignore.

I try recording these keystrokes in a macro with "use relative
references" set to either of the two possible ways, but either way, the
macro records a fixed set of cells, not the variable length block I
need in general. Why doesn't it work? And, if recording can't do this,
please give me the code that works - I get a little confused on the
syntax.

Here are the two ways it comes out (for the particular none-empty cells
I have right now), neither of which is a variable length depending upon
where the bottom of the list happens to be:

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A45").Select


or

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
Range("N18:N62").Select

Thanks!
Dean

Range("N18").Select
If Range("N18").Offset(1, 0).Value < "" Then
Range(Range("N18"), Range("N18").End(xlDown).Offset(-2, 0)).Select
End If


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Dean" wrote in message
...
For the following follow-up question, ignore the two row offset which
applies in a different worksheet. Occasionally, (in the non offset case),
there is no other entry below the first cell in question, in which case
the end-down brings us to the bottom of the worksheet, row 65536 or
whatever. Is there are a way to rewrite your command to tell the macro not
to do the end down in that event, but just select the single cell? BTW,
if the cell below is empty, all of the cells below will be also, so there
is no risk of missing something.

Thanks!
Dean

"Bob Phillips" wrote in message
...
Range(Range("N18"), Range("N18").End(xlDown).Offset(-2, 0)).Select


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Dean" wrote in message
...
He doesn't exactly tell us why the recording doesn't work, but this will
do just fine, I suppose. But will this work if I then try to go back up
by two rows?

Thanks!
Dean

"Frederik" wrote in message
...
Hello

Maybe this tip can help...

http://www.j-walk.com/ss/excel/tips/tip10.htm

It's on the site of John Walkenbach, an Excel goeroe!

greetz

"Dean" schreef in bericht
...
For keystrokes, I want my macro to go to a fixed cell location, say
cell N18, then while holding the shift key down, then do an end-down,
then move the bottom up by two rows (same thing as two cells since
it's a single column), then release the shift key. In this way, I
select a variable-length block of cells that is the whole contiguous
block except for the bottom two cells which I always want to ignore.

I try recording these keystrokes in a macro with "use relative
references" set to either of the two possible ways, but either way,
the macro records a fixed set of cells, not the variable length block
I need in general. Why doesn't it work? And, if recording can't do
this, please give me the code that works - I get a little confused on
the syntax.

Here are the two ways it comes out (for the particular none-empty
cells I have right now), neither of which is a variable length
depending upon where the bottom of the list happens to be:

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A45").Select


or

Application.Goto Reference:="R18C14"
Range(Selection, Selection.End(xlDown)).Select
Range("N18:N62").Select

Thanks!
Dean