Applying hyperlinks to autofilter results

  • Thread starter Thread starter Ahmed Mesbah
  • Start date Start date
A

Ahmed Mesbah

Hello group!

I would like to apply hyperlinks to certain values in a column. To do so, I
display all similar values that will have the same hyperlink using a custom
autofilter, select the entire column and then set up the hyperlink. The
problem is when I expand the list back again, I find that the hyperlink was
not just applied to the filtered rows, but instead all the rows that were
between the filtered values. For instance, here are three columns in my
sheet:

Row 1
Row 2
Row 1

I use autofilter to display this:

Row 1
Row 1

I select all the cells and apply the hyperlink. When I expand the list back
again, I find that Row 2 has the hyperlink as well.

Is there anyway to apply the hyperlink to just the filtered cells?

Thanks in advance.

Ahmed
 
Hi Dave

Thanks for the tip. I've managed to select the visible cells only but the
hyperlink button is now grayed out. Any ideas?

Thanks, again.

Ahmed
 
Hi Ahmed

Try running this code. To insert it push Alt+F11 and go to Insert>Module
and paste in the code. Now click the top right X to get back to Excel.
Filter as normal, select the Column, then push Alt+F8, click
"MakeHyperLinks" then "Run".

Sub MakeHyperLinks()
Dim rRange As Range, rCell As Range

Set rCell = Selection.SpecialCells(xlCellTypeVisible)
For Each rCell In rRange
rCell.Hyperlinks.Add rCell, rCell.Text
Next rCell
End Sub

The code assumes the hyperlink address is the text in the cell, if it's
not, let me know.


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Thank you very much for the macro, Dave! The hyperlinks point to files on my
local disk. Which part of the code will I need to modify to point to the
files?

Ahmed
 
You will need to change

rCell.Text

to something like

"C:\My Documents\Files\" & rCell.Text

Or, the file Workbook names do NOT have the .xls entension

"C:\My Documents\Files\" & rCell.Text & ".xls"

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Testing with Option Explicit, would not leave you open to
rRange as an undefined variable.

=
 
Please ignore the unhelpfull advise above. It has nothing to do with
Option Explicit and Rrange IS dimmensioned. I wonder about the
intentions of some, although trolling springs to mind.

Ahmed,

There was a typo in my code, it should read

Set rRange = Selection.SpecialCells

and not

Set rCell= Selection.SpecialCells

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Kudos to Dave. Worked perfectly, thank you very much. This is going to save
me tonnes of time. Have a terrific day.

Ahmed
 
Back
Top