How to determine the range selected by user?

mdocton

Board Regular
Joined
Nov 13, 2008
Messages
63
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 ...
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
What would you want to do with those values?
 

mdocton

Board Regular
Joined
Nov 13, 2008
Messages
63
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;
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Have you considered using the HYPERLINK worksheet function to create the hyperlinks?
 

mdocton

Board Regular
Joined
Nov 13, 2008
Messages
63

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

Board Regular
Joined
Nov 13, 2008
Messages
63
Yes, sorry, ... just learning about Application.Selection
Thanks for your patience :rolleyes:
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 ...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top