How to optimize this code

p45cal · #1 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

leeqiang · #2 04-17-2022, 04:15 AM

I mean the first column (column A) remains unchanged, and the second column (column B) is superlinked on top of the original, so that you can click to open the file like column A

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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