Find the last cell in a selected range?

[Guest]

I have a user selecting a range, how would I put the ending cell in the range
in a variable?

 

[RB Smissaert]

dim rng as range

set rng = selection.cells(selection.cells.count)

RBS

 

[Guest]

I guess there's no function for it because End(xldown) only goes to the first
blank cell. Thanks for the smart solution

 

[Bernie Deitrick]

Note that the code will not work if there are multiple areas selected. In that case:

Dim rng As Range
Set rng =
Selection.Areas(Selection.Areas.Count).Cells(Selection.Areas(Selection.Areas.Count).Cells.Count)

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Check your previous post, too.

I have a user selecting a range, how would I put the ending cell in the range
in a variable?

 

[Guest]

Thanks for the note.

Note that the code will not work if there are multiple areas selected. In that case:

Dim rng As Range
Set rng =
Selection.Areas(Selection.Areas.Count).Cells(Selection.Areas(Selection.Areas.Count).Cells.Count)

HTH,
Bernie
MS Excel MVP

 

[Guest]

an additional note/added caution is that that method isn't reliable: (to
illustrate from the immediate window).

Set rng =
Selection.Areas(Selection.Areas.Count).Cells(Selection.Areas(Selection.Areas.Count).Cells.Count)
? rng.Address
$D$17
? selection.Address
$F$18:$G$21,$C$10:$E$14,$D$16:$D$17


I would have expected G21 to be the result. It depends on how the user
selects the range.