I have a VBA in which I’d like all cells, in all worksheets, that begin with HTTPS to be hyperlinked.
If I leave:
Set rng = ws.UsedRange – The code doesn’t run and will not show errors.
If I define the Rng e.g. (“E1:E1000”) it hyperlinks the cells but only for that worksheet not the rest. Also want to note that not all ranges I want hyperlinked are in column E
Sub HyperlinkHTTPSTextInAllSheets()
Dim ws As Worksheet
Dim cell As Range
Dim startString As String
Dim rng As Range
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Define the range to search within the current worksheet
Set rng = ws.UsedRange
' Loop through each cell in the defined range
For Each cell In rng
' Check if the cell contains text and starts with "https"
If VarType(cell.Value) = vbString And cell.Value Like "https*" Then
' Create a hyperlink for the text
startString = cell.Value
' Remove existing hyperlink if present to avoid duplication
If cell.Hyperlinks.Count > 0 Then cell.Hyperlinks.Delete
ws.Hyperlinks.Add Anchor:=cell, Address:=startString, TextToDisplay:=startString
End If
Next cell
Next ws
End Sub
allen martinez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.