Below is my code for updating hyperlink addresses that were corrupted. I can update the hyperlinks in very small batches, but the sheet has almost 3000 rows. All of the hyperlinks are in column B. Any suggestions or code optimization would be very helpful.
Code:
Sub FixHLinks(sFind As String, sReplace As String, _
Optional lStart As Long = 1, Optional lCount As Long = -1)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim rCell As Range
Dim hl As Hyperlink
'THIS WAS FOR TESTING PURPOSES
For Each rCell In ActiveSheet.Range("B461:B561")
'THIS IS IDEAL
'For Each rCell In ActiveSheet.UsedRange.Cells
If rCell.Hyperlinks.Count > 0 Then
For Each hl In rCell.Hyperlinks
hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
Next hl
End If
Next rCell
Set rCell = Nothing
Set hl = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
'THE ABOVE CODE IS CALLED FROM THE CODE BELOW
Sub FixThem()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
FindReplaceHLinks "../AppData/Roaming/Microsoft/Excel/", "\\NetworkShare\Folder1\Folder2\Folder3\"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub