SOLVED

Using VBA to find cells that contain a hyperlink

Brass Contributor

Hello,

I have a little bit of code that I need some assistance with.

I am searching for all hyperlinks and I want to get every cell that contains a hyperlink

 

I have  the following that gives me the URL but I have no idea what cell contains the link

Dim LinkCount As Long

Dim URL as string

    For LinkCount = 1 To ActiveSheet.Hyperlinks.Count

        URL = ActiveSheet.Hyperlinks(lngLinkCount).Address

    Next LinkCount

 

Thanks in advance

2 Replies
best response confirmed by Gilgamesh1964 (Brass Contributor)
Solution

@Gilgamesh1964 

The Range property of a hyperlink represents the cell that contains the hyperlink.

    Dim LinkCount As Long
    Dim URL As String
    dim Rng as Range
    For LinkCount = 1 To ActiveSheet.Hyperlinks.Count
        URL = ActiveSheet.Hyperlinks(lngLinkCount).Address
        Set Rng = ActiveSheet.Hyperlinks(lngLinkCount).Range
        ' If you want to know the cell address, use Rng.Address
    Next LinkCount
Thank You
Works great
1 best response

Accepted Solutions
best response confirmed by Gilgamesh1964 (Brass Contributor)
Solution

@Gilgamesh1964 

The Range property of a hyperlink represents the cell that contains the hyperlink.

    Dim LinkCount As Long
    Dim URL As String
    dim Rng as Range
    For LinkCount = 1 To ActiveSheet.Hyperlinks.Count
        URL = ActiveSheet.Hyperlinks(lngLinkCount).Address
        Set Rng = ActiveSheet.Hyperlinks(lngLinkCount).Range
        ' If you want to know the cell address, use Rng.Address
    Next LinkCount

View solution in original post