Hi All,

I have a situation where l need the user to select a multiple range of
cells in a single column by holding down the 'Ctrl' key and selecting
the cells. The cells are not likely to be contiginous.

Can anybody provide the code (or point me in the right direction) to
retrieve the cell addresses / rows once the selection has been made?

TIA

Regards

Michael Beckinsale

Sub retriever()
Dim r As Range
Dim s As String
s = ""
For Each r In Selection
s = s & " " & r.Address
Next
MsgBox (s)
End Sub
--
Gary's Student


"michael.beckinsale" wrote:

Hi All,

I have a situation where l need the user to select a multiple range of
cells in a single column by holding down the 'Ctrl' key and selecting
the cells. The cells are not likely to be contiginous.

Can anybody provide the code (or point me in the right direction) to
retrieve the cell addresses / rows once the selection has been made?

TIA

Regards

Michael Beckinsale

Hi
Selection.Address

will give you the address as a textstring.
NOTE:
If this string is long (more than 1024 characters, possibly less?) -
which occurs if there are lots of pieces in the selection, it can be
difficult to put this address back into a range object

e.g. Range(Selection.Address)

can give an error. In that case, you have to break the string up and
use Union to get the range back.
I have several functions for dealing with address strings if you need
them.
regards
Paul

michael.beckinsale wrote:
Hi All,

I have a situation where l need the user to select a multiple range of
cells in a single column by holding down the 'Ctrl' key and selecting
the cells. The cells are not likely to be contiginous.

Can anybody provide the code (or point me in the right direction) to
retrieve the cell addresses / rows once the selection has been made?

TIA

Regards

Michael Beckinsale

Paul / Gary's Student.

Many thanks for your input.

Paul l understand what you are saying about the address text strings
but in this case it wont be a problem, but l would be interested in
your functions to help overcome the problem.

I eventually got my brain into gear and came up with the following code
which does what l want. The 'prev' variable is used to ensure that if
rows 1 or 65536 are selected they are not returned twice.

Sub RowsFromSelectedCells()

Dim prev As Long
Dim c As Range
prev = 0
For Each c In Selection
If c.Row < prev Then
MsgBox (c.Row) <<<<< enter required code here
End If
prev = c.Row
Next c

End Sub

Again thanks for your help,

Regards

Michael Beckinsale

One way would be to loop through the Areas collection.

Dim A As Range
Dim S As String
If TypeOf Selection Is Excel.Range Then
For Each A In Selection.Areas
S = S & " " & A.Address
Next A
MsgBox S
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

"michael.beckinsale" wrote in message
oups.com...
Hi All,

I have a situation where l need the user to select a multiple range of
cells in a single column by holding down the 'Ctrl' key and selecting
the cells. The cells are not likely to be contiginous.

Can anybody provide the code (or point me in the right direction) to
retrieve the cell addresses / rows once the selection has been made?

TIA

Regards

Michael Beckinsale