Syntax again for DA

[Dean]

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

 

[Frederik]

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]

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

 

[Bob Phillips]

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]

Thanks, Bob.

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]

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]

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]

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

 

[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]

Again, can anyone tell me why?

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]

Still hoping for some help...

Again, can anyone tell me why?