MsgBox with information on selected cells

[andreashermle]

Dear Experts:

I would like to have a macro that shows me ...

the number of selected cells in a column (contiguous range) and ...
the cell reference of the upper most cell selected.

Example: Range selected: A2:A13 (only selections in one column, no
selections spanning 2 or more columns)

The macro is to inform in a MsgBox as follows:

12 cells selected, starting in A2

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 

[Patrick Molloy]

Sub message1()

MsgBox Selection.Count & " cells from " & ActiveCell.Address(False, False)


End Sub

 

[Mike Fogleman]

Patrick, suppose you started in A13 and selected upwards to A2, your code
would show A13 as the uppermost cell. Try this modification:

Sub message1()
MsgBox Selection.Count & " cells from " & Selection.Cells(1,
1).Address(False, False)
End Sub

Mike F

 

[Jay]

Hi Andreas -

Here's one way:

MsgBox Selection.Cells.Count & " cells selected, starting in " & _
Selection.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

 

[Peter T]

Yet another way, to cater for possibility of a multi area selection

Sub test()
Dim i As Long
Dim sMsg
Dim rng As Range

Set rng = Selection

With rng.Areas(1)
sMsg = .Count & " cell(s) " & .Address(0, 0)
End With
For i = 2 To rng.Areas.Count
With rng.Areas(i)
sMsg = sMsg & vbCr & .Count & " cell(s) " & .Address(0, 0)
End With
Next

MsgBox sMsg

End Sub

Regards,
Peter T

 

[Mike Fogleman]

To further warn of non-contiguous or more than 1 column:

Sub message1()
If Selection.Areas.Count > 1 Then
MsgBox "Selection is non-contigous"
Exit Sub
ElseIf Selection.Columns.Count > 1 Then
MsgBox "More than 1 column selected"
Exit Sub
Else
MsgBox Selection.Count & " cells from " _
& Selection.Cells(1, 1).Address(False, False)
End If
End Sub

Mike F

 

[Mike Fogleman]

Spelling of "contiguous" in first Msgbox is wrong - Sorry

Mike F

 

[andreashermle]

Sub message1()

MsgBox Selection.Count & " cells from " & ActiveCell.Address(False, False)

End Sub













- Show quoted text -

Dear Patrick,

terrific help. Working as desired. Thank you. Regards, Andreas

 

[andreashermle]

Patrick, suppose you started in A13 and selected upwards to A2, your code
would show A13 as the uppermost cell. Try this modification:

Sub message1()
MsgBox Selection.Count & " cells from " & Selection.Cells(1,
1).Address(False, False)
End Sub











- Show quoted text -

Dear Mike,

thank you for making Patrick's code even a little better. Terrific
help. Regards, Andreas

 

[andreashermle]

Hi Andreas -

Here's one way:

MsgBox Selection.Cells.Count & " cells selected, starting in " & _
Selection.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

----
Jay












- Show quoted text -

Dear Jay,

great help. It works as desired. Thank you very much. Regards, Andreas

 

[andreashermle]

To further warn of non-contiguous or more than 1 column:

Sub message1()
If Selection.Areas.Count > 1 Then
    MsgBox "Selection is non-contigous"
    Exit Sub
ElseIf Selection.Columns.Count > 1 Then
    MsgBox "More than 1 column selected"
    Exit Sub
Else
    MsgBox Selection.Count & " cells from " _
    & Selection.Cells(1, 1).Address(False, False)
End If
End Sub








- Show quoted text -

Dear Mike,

although all of your collegues' solutions fullfill my requirements
fully, yours is even a little bit more sophisticated. Great / terrific
help. Thank you very much . Regards, Andreas

 

[andreashermle]

Yet another way, to cater for possibility of a multi area selection

Sub test()
Dim i As Long
Dim sMsg
Dim rng As Range

    Set rng = Selection

    With rng.Areas(1)
        sMsg = .Count & " cell(s)  " & .Address(0, 0)
    End With
    For i = 2 To rng.Areas.Count
        With rng.Areas(i)
            sMsg = sMsg & vbCr & .Count & " cell(s)  " & ..Address(0, 0)
        End With
    Next

    MsgBox sMsg

End Sub

Regards,
Peter T













- Show quoted text -

Dear Peter,

very nice VBA solution. Great help. Thank you very much. Regards,
Andreas