SOLVED

Using VBA to find cells that contain a hyperlink

%3CLINGO-SUB%20id%3D%22lingo-sub-3244392%22%20slang%3D%22en-US%22%3EUsing%20VBA%20to%20find%20cells%20that%20contain%20a%20hyperlink%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3244392%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20little%20bit%20of%20code%20that%20I%20need%20some%20assistance%20with.%3C%2FP%3E%3CP%3EI%20am%20searching%20for%20all%20hyperlinks%20and%20I%20want%20to%20get%20every%20cell%20that%20contains%20a%20hyperlink%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%26nbsp%3B%20the%20following%20that%20gives%20me%20the%20URL%20but%20I%20have%20no%20idea%20what%20cell%20contains%20the%20link%3C%2FP%3E%3CP%3E%3CSPAN%3EDim%20LinkCount%20As%20Long%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDim%20URL%20as%20string%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%22%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20LinkCount%20%3D%201%20To%20ActiveSheet.Hyperlinks.Count%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%22%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20URL%20%3D%20ActiveSheet.Hyperlinks(lngLinkCount).Address%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%22%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%20LinkCount%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3E%3CSPAN%3EThanks%20in%20advance%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3244392%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3244732%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VBA%20to%20find%20cells%20that%20contain%20a%20hyperlink%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3244732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F920454%22%20target%3D%22_blank%22%3E%40Gilgamesh1964%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Range%20property%20of%20a%20hyperlink%20represents%20the%20cell%20that%20contains%20the%20hyperlink.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20Dim%20LinkCount%20As%20Long%0A%20%20%20%20Dim%20URL%20As%20String%0A%20%20%20%20dim%20Rng%20as%20Range%0A%20%20%20%20For%20LinkCount%20%3D%201%20To%20ActiveSheet.Hyperlinks.Count%0A%20%20%20%20%20%20%20%20URL%20%3D%20ActiveSheet.Hyperlinks(lngLinkCount).Address%0A%20%20%20%20%20%20%20%20Set%20Rng%20%3D%20ActiveSheet.Hyperlinks(lngLinkCount).Range%0A%20%20%20%20%20%20%20%20'%20If%20you%20want%20to%20know%20the%20cell%20address%2C%20use%20Rng.Address%0A%20%20%20%20Next%20LinkCount%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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 (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