答案1
这将“重置”每个包含超链接的单元格(公式类型超链接):
Sub HyperResetter()
Dim r As Range, f As String
For Each r In Cells.SpecialCells(xlCellTypeFormulas)
f = r.Formula
If InStr(1, f, "=HYPERLINK") > 0 Then
r.Clear
r.Formula = f
End If
Next r
End Sub
文本颜色将被修复,但其他特殊格式也将被重置。
答案2
继@pat2015 的评论后
我使用 VBA 重置每个超链接:
Sub ResetHyper()
' Select HyperLinks
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
' Clear Current HyperLink
Selection.ClearContents
' Rebuild HyperLink
ActiveCell.FormulaR1C1 = _
"=HYPERLINK(""http://api.wunderground.com/api/eec4c24fa3e74d09/history_20171110/q/""&RC[-2]&""/""&RC[-5]&"".json"")"
' Select and Copy Down
Range("H2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H2:H750")
Range("H2:H750").Select
End Sub
答案3
这将重置超链接工作表公式和指向活动工作表的各个单元格的超链接。第一个循环查找电子表格公式中的超链接。删除公式并重写它将已跟踪链接重置为未跟踪链接。第二个循环查找链接单元格。它通过获取单元格和地址并重新链接它们来重置超链接。原始链接无需删除。要显示的文本不需要包含在内才能正常工作,而且 Excel 365 似乎无论如何都无法获取它。我删除了“If rngCell.HasFormula = False Then”中与超链接无关的内容。我保留它是因为“If rngCell.HasFormula and InStr(rngCell.Formula, "HYPERLINK") > 0”实际上是一个嵌套的 if,这种方式对我来说似乎更简洁。
'2020-01-30
'reset all hyperlinks to unfollowed in active sheet
Sub fResetHyperlinks()
Dim hypLink As Hyperlink
Dim rngCell As Range
Dim strFormula As String
Dim strAddress As String
Dim strSubAddress As String
For Each rngCell In ActiveSheet.UsedRange
If rngCell.HasFormula = False Then 'look for formulas
ElseIf InStr(rngCell.Formula, "HYPERLINK") > 0 Then 'containing hyperlinks
strFormula = rngCell.Formula 'remember hyperlink formula
rngCell.Clear 'erase hyperlink
rngCell.Formula = strFormula 'recreate
End If
Next
For Each hypLink In ActiveSheet.Hyperlinks 'look for hyperlinked cells
Set rngCell = hypLink.Range 'get cell
strAddress = hypLink.Address 'get addresses
strSubAddress = hypLink.SubAddress
If strSubAddress = "" Then 'can't use subaddress if empty
ActiveSheet.Hyperlinks.Add Anchor:=rngCell, Address:=strAddress
Else 'doesn't want or need display text
ActiveSheet.Hyperlinks.Add Anchor:=rngCell, Address:=strAddress, SubAddress:=strSubAddress
End If
Next
End Sub