Forum Discussion

Gilgamesh1964's avatar
Gilgamesh1964
Brass Contributor
Mar 03, 2022
Solved

Using VBA to find cells that contain a hyperlink

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

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Mar 03, 2022

    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

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 03, 2022

    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

Resources