determine whether the activecell is on the top or bottom

[John Smith]

In a user selected single column selection, how do I determine
whether the activecell is on the top or bottom of the selection?
Thanks.

 

[Rick Rothstein]

Just to you know, the ActiveCell can be located interior to the Selection
(make a Selection and then press the Tab key a few times). Given that, here
is some code you can use...

Location = InStr(Selection.Address, ActiveCell.Address)
If Location = 0 Then
MsgBox "ActiveCell is not at either end of the Selection"
ElseIf Location = 1 Then
MsgBox "ActiveCell is at the beginning of the Selection"
Else
MsgBox "ActiveCell is at the end of the Selection"
End If

 

[Dave Peterson]

You could compare rows:

Dim myRng as range
dim myCell as range

set myrng = selection.areas(1) 'or something specific???
set mycell = activecell 'or anything you want.

msgbox myrng.row & vblf & myrng.rows(myrng.rows.count).row & vblf & mycell.row

 

[Rick Rothstein]

I guess I should mention that my code assumes the selection is contiguous.
If it is not contiguous, then you will have to tell us what the beginning
and end of the selection means as the selection is constructed in the order
the cell or cells are added to it... that means the first and/or last
selected area could be between what looks like outer areas. For example,
select these areas in the order shown (hold down the Control key while
selecting them)... B2:C5, then A7:C9, then E1:G4 and then E6:F8. Now execute
this line in the Immediate Window...

? Selection.Address

and note the order the addresses are printed in... it's the same order you
selected them in. So, which to you consider the first area and which is the
last? Before you answer too quickly, note the top left area is does not have
the leftmost nor topmost cell in it; nor does the bottom right one contain
the rightmost or bottommost cell