CODEWITHSUNDEEP
vbaexcel
Brian
Brian

Reputation: 31

Efficient way to create thousands of hyperlinks using Excel VBA

I have a VBA question for creating hyperlinks for a range of cells.

In my application, I have a table of data where one column lists IDs that I would like to add hyperlinks to to open a corresponding report/page for that ID.

In all the following cases assume all the cells are populated with numeric IDs.

I am currently using code very similar to the following (simplified for this example):

Dim r As Range, c As Range
Set r = Range("C1:C60000")
For Each c In r
   ActiveSheet.Hyperlinks.add anchor:=c, address:="http://www.url.com?address=" &  c.value
Next c

However, as the range gets larger - the time for this code to execute gets exceedingly longer. In the case where the URL is always the same the following code runs quite quickly.

 Dim r as Range
 set r = Range("A1:A60000")
 r.Hyperlinks.add anchor:=r, address:="http://www.url.com"

My question is when the URLs are different, specifically if they incorporate data from the cell there is there a way that is more efficient than what I'm currently doing.

Any help or insight would be appreciated. Thank you,

Example Data (in an CSV format and as an Excel Table screenshot), I need to be able to add hyperlinks to the entire fist column in the format of "http://www.urltest.com/sample?id=[ID]" where [ID] is the value from the cell.

Example Table

Upvotes: 3

Views: 2465

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Fastest way I can think of is using the HYPERLINK Function and assign the values to all cells in one go :) This will also ENSURE that you will NOT have to loop.

The syntax of Hyperlink is

HYPERLINK(link_location, [friendly_name])

Read about it in Excel help or Google it.

The ADDRESS(ROW(),COLUMN())) give you the address of the current cell.

Try this

Sub test_simple_diffurl()
    Dim r As Range

    Set r = Range("C1:C60000")

    r.Formula = "=HYPERLINK(""http://www.url.com?address="" & ADDRESS(ROW(),COLUMN()),""Test with "" & ADDRESS(ROW(),COLUMN()))"
End Sub

Upvotes: 5

Related Questions