Hyperlink covers several cells, how to make it linkable from any of

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
Hello.
I have a link in cell A5, but it covers another 3-4 rows.
How to merge the columns, so it would work clicking only part of the expression?

Below are initial

Initial vba for the link creation if it helps. I have 3 of them of different type. and in attachment how it looks at the moment.
Here is the 1st one.
Sub Hyper()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "HOTELS" Then
ws.Hyperlinks.Add _
Anchor:=ws.Range("B5"), _
Address:="", _
SubAddress:="HOTELS!A1", _
TextToDisplay:="Back To The Hotel List"
End If
Next ws
MsgBox ("Completed")
End Sub
 

Attachments

  • merge link.png
    merge link.png
    3.4 KB · Views: 2

xenios

Board Regular
Joined
Sep 4, 2020
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
Not your fault, it's mine. We choose what we get involved in 🤣


Then you just have an hyperlink.
Turn on the macro recorder and carry out the actions in the section "Find All Hyperlinks Linked to Specific Text in Excel" in the link below

Then at the bottom of the code add the lines

VBA Code:
    Dim Findrange As Range
    Set Findrange = Columns("A:A").Find("Back to The Hotel List", , xlValues, xlWhole, xlByRows, xlNext, False)
    If Not Findrange Is Nothing Then Findrange.Resize(, 4).merge

and test. Remember when you put it in your loop you need to add the WS. to the range.

Then do the same with the other word, changing the word and the column in the code (make sure that your wording in the find is correct as your image just shows "Top" not "To the Top").
Works!!!!!!!!:love::love::love::love::love::love::love::love:
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,129,996
Messages
5,639,448
Members
417,090
Latest member
schoelleya

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