How to optimize this code

p45cal · #2 04-17-2022, 04:01 AM

Without changing your code, in the first column I get the likes of:
=HYPERLINK("C:\Users\Public\Documents\10001.txt"," 10001.txt")
while in the second column I get:
C:\Users\Public\Documents\10001.txt

Clicking on the first column takes me to the correct file, so at the moment I don't understand: "to ensure that clicking on the link can open the file".

Otherwise, it's not difficult to get the 2nd parameter to look like:
=HYPERLINK("C:\Users\Public\Documents\10000.txt"," C:\Users\Public\Documents\10000.txt")
but as far as I'm aware it won't make any difference to whether it opens the correct file or not.

You can do that by changing:
arr(i, 1) = "=hyperlink(""" & arr(i, 2) & """,""" & arr(i, 1) & """)"
to:
arr(i, 1) = "=hyperlink(""" & arr(i, 2) & """,""" & arr(i, 2) & """)"

another way to get hyperlinks is to add them without the formula, either by changing to:

Code:

With ActiveSheet
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
  r = .Range("a" & Rows.Count).End(3).Row
  For rw = 2 To UBound(arr)
        .Hyperlinks.Add Anchor:=.Cells(rw, 1), Address:=arr(rw, 2), TextToDisplay:=arr(rw, 2)
  Next rw
  .Range("a1:f" & r).Borders.LineStyle = xlContinuous
  .Range("a1:f" & r).Borders.Weight = xlThin
End With

or:

Code:

With ActiveSheet
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
  r = .Range("a" & Rows.Count).End(3).Row
  For Each cll In .Range("A2:A" & r).Cells
      .Hyperlinks.Add Anchor:=cll, Address:=cll.Offset(, 1).Value, TextToDisplay:=cll.Offset(, 1).Value
  Next cll
  .Range("a1:f" & r).Borders.LineStyle = xlContinuous
  .Range("a1:f" & r).Borders.Weight = xlThin
End With

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to optimize the size of a slide for export?	Hornswoggled	PowerPoint	0	12-21-2020 09:05 PM
How to optimize the data summation code for each row and column in a data rectangle area?	leeqiang	Excel Programming	5	10-04-2020 06:53 PM
How do I optimize images for Word?	fluoresce	Drawing and Graphics	1	02-08-2017 06:59 PM
VBA Code to search for field codes with certain text before the Field code and to change style	welcometocandyland	Word VBA	4	02-08-2017 06:53 PM
How to optimize an Excel file	orosos	Excel	2	04-16-2015 01:27 PM