Find 1st Column with a Hyperlink

[stuartgb100]

I have code that inserts a row if a hyperlink is found:
Sub If_Hyperlink_Insert_Row()

Dim ws As Worksheet
Dim c, Rng As Range

Set ws = ActiveSheet
    With ws
        'Amend for required column
        Set Rng = ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp))
        For Each c In Rng
            If c.Hyperlinks.Count > 0 Then ' has a hyperlink
                Rows(c.Row + 1).Insert
                Rows(c.Row + 1).RowHeight = 5
                Set c = c.Offset(1, 0)
            End If
        Next
    End With
End Sub

This uses a specified sheet & column.
I need to run this on all sheets in the activeworkbook (using For Each etc),
BUT the column containing the hyperlinks are different.

How do I find that column please ?

Thanks.

 

[Edgar_]

How do I find that column please ?

Your sheets have a Hyperlink member where you can extract all your hyperlinks. Each hyperlink in that collection will have a Range member where you can extract the column if that's what you want, but I suspect all you need is to get the link address and add a row below, so something like this should work:

Sub fjlka()
    Dim ws As Worksheet, link As Hyperlink
    For Each ws In ThisWorkbook.Sheets
        For Each link In ws.Hyperlinks
            link.Range.Offset(1, 0).EntireRow.Insert
        Next link
    Next ws
End Sub

 

[stuartgb100]

Many thanks (again !).
Regards.

 

[Edgar_]

If you still needed to know the first column with links, this would work

Sub FindFirstColumnWithHyperlinkBySheet()
    Dim ws As Worksheet
    Dim link As Hyperlink
    Dim linksCount As Long
    Dim arr()
    Dim i As Long
    
    For Each ws In ThisWorkbook.Sheets
    
        linksCount = ws.Hyperlinks.Count
        
        If linksCount > 0 Then
            ReDim arr(1 To linksCount)
            i = 1
            
            For Each link In ws.Hyperlinks
                arr(i) = link.Range.Column
                i = i + 1
            Next link
            
            Debug.Print Application.WorksheetFunction.Min(arr)
            
        End If
        
        Erase arr
        
    Next ws
End Sub

 

[stuartgb100]

Yes, that's what I was looking for.
Many thanks again.
Regards.