1

I created a column B of hyperlinks by using the formula =hyperlink(a1) etc. Now I'd like to copy and paste the hyperlinks into col C in a way that gets rid of the formula so that I can delete column A.

The command "paste as hyperlink" doesn't seem to work on a whole column of data, it only seems to work on a single cell at a time.

IdRatherBeGardening
  • 65
  • 2
  • 10

1 Answers1

0

One way is to parse the formula. If we have:

enter image description here

Then something like:

Sub HyperConverter()
   Dim r As Range

   For Each r In Range("B:B").Cells.SpecialCells(xlCellTypeFormulas)
      s = r.Formula
      If InStr(1, s, "HYPER") > 0 Then
         ary = Split(s, Chr(34))
         ActiveSheet.Hyperlinks.Add Anchor:=r.Offset(0, 1), Address:=ary(1), TextToDisplay:=ary(3)
      End If
   Next r
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99