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

Thanks, Bob. I can't believe I am messing this up. Here is the front of my
macro, adapted from what you supplied:

Sheets("Output").Select
ActiveSheet.Unprotect
Application.Goto Reference:="R18C2"
ActiveCell.Select

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

Selection.Copy
Sheets("Joe").Select
Application.Goto Reference:="R11C18"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False ' this row may not be needed
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.Copy
ActiveCell.Offset(0, -17).Range("A1").Select 'now we will past into
column A of the REPORT sheet
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

As I understand it, it should go to cell B18 of Output. Then, if B19 is
empty, it should copy cell B18 to cell R11 of "Joe". Then, since there is
also now only one entry in the pasted area of Joe, it should leave it in the
same place after sorting. Then, it should copy this one cell back to cell
A11 of Joe.

Cell A11 is Ok despite the fact that it seems that the above macro is first
copying to cell R12, not R11, of Joe, which I do not understand. Can
someone tell me why it is doing this? Doesn't Range("A1).Select mean to
select the current cell? Kindly put a comment beside any lines in the macro
that you think I don't understand.

Thanks!
Dean





"Bob Phillips" wrote in message
...
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

Since Bob does not seem to be around and it's been a few days, can anyone
else tell me why the macro below is copying to cell R12, not R11.

Thanks!
Dean

"Dean" wrote in message
...
Thanks, Bob. I can't believe I am messing this up. Here is the front of
my macro, adapted from what you supplied:

Sheets("Output").Select
ActiveSheet.Unprotect
Application.Goto Reference:="R18C2"
ActiveCell.Select

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

Selection.Copy
Sheets("Joe").Select
Application.Goto Reference:="R11C18"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False ' this row may not be needed
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.Copy
ActiveCell.Offset(0, -17).Range("A1").Select 'now we will past into
column A of the REPORT sheet
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

As I understand it, it should go to cell B18 of Output. Then, if B19 is
empty, it should copy cell B18 to cell R11 of "Joe". Then, since there is
also now only one entry in the pasted area of Joe, it should leave it in
the same place after sorting. Then, it should copy this one cell back to
cell A11 of Joe.

Cell A11 is Ok despite the fact that it seems that the above macro is
first copying to cell R12, not R11, of Joe, which I do not understand.
Can someone tell me why it is doing this? Doesn't Range("A1).Select mean
to select the current cell? Kindly put a comment beside any lines in the
macro that you think I don't understand.

Thanks!
Dean





"Bob Phillips" wrote in message
...
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

Again, can anyone tell me why?

"Dean" wrote in message
...
Since Bob does not seem to be around and it's been a few days, can anyone
else tell me why the macro below is copying to cell R12, not R11.

Thanks!
Dean

"Dean" wrote in message
...
Thanks, Bob. I can't believe I am messing this up. Here is the front of
my macro, adapted from what you supplied:

Sheets("Output").Select
ActiveSheet.Unprotect
Application.Goto Reference:="R18C2"
ActiveCell.Select

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

Selection.Copy
Sheets("Joe").Select
Application.Goto Reference:="R11C18"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False ' this row may not be needed
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.Copy
ActiveCell.Offset(0, -17).Range("A1").Select 'now we will past into
column A of the REPORT sheet
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

As I understand it, it should go to cell B18 of Output. Then, if B19 is
empty, it should copy cell B18 to cell R11 of "Joe". Then, since there
is also now only one entry in the pasted area of Joe, it should leave it
in the same place after sorting. Then, it should copy this one cell back
to cell A11 of Joe.

Cell A11 is Ok despite the fact that it seems that the above macro is
first copying to cell R12, not R11, of Joe, which I do not understand.
Can someone tell me why it is doing this? Doesn't Range("A1).Select mean
to select the current cell? Kindly put a comment beside any lines in the
macro that you think I don't understand.

Thanks!
Dean





"Bob Phillips" wrote in message
...
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