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

"andreashermle" wrote in message
...
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, 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
"Patrick Molloy" wrote in message
...

Sub message1()

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


End Sub

"andreashermle" wrote in message
...
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

Hi Andreas -

Here's one way:

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

----
Jay

"andreashermle" wrote:

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
.

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




"andreashermle" wrote in message
...
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

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" wrote in message
...
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
"Patrick Molloy" wrote in message
...

Sub message1()

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


End Sub

"andreashermle" wrote in message
...
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

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

Mike F
"Mike Fogleman" wrote in message
...
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" wrote in message
...
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
"Patrick Molloy" wrote in message
...

Sub message1()

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


End Sub

"andreashermle" wrote in message
...
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

On Dec 18, 11:49*am, "Patrick Molloy"
wrote:
Sub message1()

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

End Sub

"andreashermle" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Dear Patrick,

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

On Dec 18, 12:24*pm, "Mike Fogleman" wrote:
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"Patrick Molloy" wrote in message

...





Sub message1()

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

End Sub

"andreashermle" wrote in message
....
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- Hide quoted text -

- Show quoted text -

Dear Mike,

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

On Dec 18, 12:25*pm, Jay wrote:
Hi Andreas -

Here's one way:

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

----
Jay



"andreashermle" wrote:
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
.- Hide quoted text -

- Show quoted text -

Dear Jay,

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

On Dec 18, 12:57*pm, "Mike Fogleman" wrote:
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" wrote in message

...



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
"Patrick Molloy" wrote in message
...

Sub message1()

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

End Sub

"andreashermle" wrote in message
....
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- Hide quoted text -

- 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

On Dec 18, 12:49*pm, "Peter T" <peter_t@discussions wrote:
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

"andreashermle" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Dear Peter,

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