MsgBox with information on selected cells

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

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
 
Sub message1()

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


End Sub
 
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
 
Hi Andreas -

Here's one way:

MsgBox Selection.Cells.Count & " cells selected, starting in " & _
Selection.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
 
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
 
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
 
Sub message1()

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

End Sub













- Show quoted text -
Click to expand...

Dear Patrick,

terrific help. Working as desired. Thank you. Regards, Andreas
 
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 -
Click to expand...

Dear Mike,

thank you for making Patrick's code even a little better. Terrific
help. Regards, Andreas
 
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 -
Click to expand...

Dear Jay,

great help. It works as desired. Thank you very much. Regards, Andreas
 
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 -
Click to expand...

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
 
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 -
Click to expand...

Dear Peter,

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