If Cell Contains Hyperlink Count

[ktkelly_1]

Hello I am trying to construct a vba macro to count everytime a cell contains a hyperlink in range of data. Currenly having it search the row for a hyperlink and then if it finds a hyperlink it will count and not delete the row, deleting the row if it doesn't contain a row. I am currently having trouble defining the hyperlink so if the active cell value contains a hyperlink that says "jama" in it, the macro will count it. Any ideas?

Sheets("Issue Navigator").Activate
Range("A1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value <> "" Then
TotalVal = TotalVal + 1
ActiveCell.Offset(0, 1).Select
Else
End If
TotalVal = TotalVal + 1
ActiveCell.Offset(0, 1).Select
Loop


UpperBound = Range("A2").Select
Selection.End(xlDown).Select
LowerBound = ActiveCell.Row - 1


Range("A2").Select


TotalValNoChange = TotalVal
'This is the section I am trying to count the hyperlink
Dim HL As Hyperlink
For i = 1 To LowerBound
Do While TotalVal > 0
If ActiveCell.Value <> HL Then
Else
JamaCount = JamaCount + 1
End If
ActiveCell.Offset(0, 1).Select
TotalVal = TotalVal - 1
Loop
If JamaCount <= 0 Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
Range("A" & ActiveCell.Row).Select
End If
TotalVal = TotalValNoChange
JamaCount = 0
Next i
'
End Sub
Posted on Microsoft Community: https://answers.microsoft.com/en-us...-7a8e-4588-bd85-22a5e9646f51?tm=1562601785850

 

[Domenic]

To check whether a cell contains a hyperlink, and whether the hyperlink displays the text "jama", try...

    If ActiveCell.Hyperlinks.Count > 0 Then
        If UCase(ActiveCell.Hyperlinks(1).TextToDisplay) = "JAMA" Then
            'increment count
        End If
    End If

Note that UCase(...) = "JAMA" is used so that the comparison is not case-sensitive.

Hope this helps!