How to determine the range selected by user?

[mdocton]

Hi,
I know that there are ways to perform macros just on the cells that a user has selected and, although an amatuer, I've got them to work.
What I have been trying to find out how to determine their references as variables.

For example, the user has selected the range A1:C6.
Can I gather "A", "1", "C" and "6" as variables?

Thanks in advance ...

 

[Norie]

What would you want to do with those values?

 

[mdocton]

I'm working on creating hyperlinks from cell values in a range. The source sheet (supplied from an external source) always has over 65000 rows, mostly blank, so the macro takes ages if the user selects the whole column.
I'm trying to work out a way of avoiding that and have been looking at the range selected by the user and wondered if they could be captured.

At present I've got this bit;

Dim rng As Range
 For Each rng In Application.Selection
        If Cells(rng.Row, rng.Column).Value = "" Then
        Else
        Application.ActiveSheet.Hyperlinks.Add rng, Currpath & "\" & rng.Value
        End If
 Next rng

and I'm trying to work out how to abort it once it gets to the last active row.

As I say, I'm an amatuer but like trying to work it out, mostly through google and reading the threads here.

I thought that I could use those values in my (fairly simple) macros ...

 

[Norie]

Have you considered using the HYPERLINK worksheet function to create the hyperlinks?

 

[mdocton]

I've looked at it and that'd work too. It would solve the 'last row' problem.
What I can't quite get is if the user only wants to do it on 3 cells, e.g. "M20:M22" and has selected those cells.
That's why I was trying to work out the user selected range, I think!

I'll investigate the HYPERLINK function further ... Thanks

 

[Norie]

I'm a little confused, the code you posted does work with the user selected range, that's what Application.Selection is.

If the problem is that the user is selecting entire columns, or rows, then the code could be adapted to exit the loop when the first empty cell is found, or something similar.

Dim rng As Range

    For Each rng In Application.Selection
        If rng.Value = "" Then
            Exit For
        Else
            Application.ActiveSheet.Hyperlinks.Add rng, Currpath & "\" & rng.Value
        End If
    Next rng

 

[mdocton]

Yes, sorry, ... just learning about Application.Selection
Thanks for your patience
Your answer is what I'm looking at ... even thought the sheet has 65000 rows, I have recorded the last 'real' row as (e.g.) 850 ... that's where I'd like to exit ...