Last cell in selected range

[VBA-addict]

This issue is widely discussed in the Internet, but for simplistic cases - when we have a rectangular Selection or when it goes about last filled in cell on a Worksheet.

My question is different: Suppose we selected with Ctrl single cells: C5;A1;B3 - I need to get C5 as first cell then and B3 as last one.
Actually Selection.Address gives the following:
$C$5,$A$1,$B$3 - which is correct, but

Selection.Cells(Selection.Cells.Count).Address
gives you not B3 as it may be expected but $C$7

Selection.SpecialCells(xlCellTypeLastCell).Address
gives you Right Bottom cell
$C$5

The only solution I found so far, to parse Selection.Address with InStr and InStrRev Functions to find "$" as pivots to extract addresses ("," would not be used for that as I'm not sure for other Excels if it's "," or ";" and then for rectangular selection it will be ":")

 

[Ruddles]

Do a For Each variable in Selection and save the address of the first and last cell?

 

[VBA-addict]

Do a For Each variable in Selection and save the address of the first and last cell?

What you propose works, but I believe that for big selections it will work slower than 2 textual searches.

Theoretically Selection is an array. You can even get it's dimension by .Count method. But the issue is how to address elements of that array properly.

 

[Ruddles]

You're probably correct as regards speed.

However I think Selection is a Range object or a Collection of Range objects rather an array. I don't think arrays have a .Count property.

 

[VBA-addict]

Solved.
It's happened that Excel allocate single cells selection through Areas
Then it can be addressed the following way:

    AreasNumber = Selection.Areas.count
    If AreasNumber >= 1 Then
        FirstCellAddress = Selection.Areas(1).Cells(1).Address
        LastCellAddress = Selection.Areas(AreasNumber).Cells(Selection.Areas(AreasNumber).Cells.count).Address
    End If

 

[Ruddles]

Yoink - noted!