hyperlink check - macros

[Lochnagar]

Hi All,

I have a list of documents in excel (i.e. a document per cell), with some hyperlinked to their respective locations. What I'm wanting to do is delete the cells that contain a documents name, which isn't hyperlinked. And keep the ones with hyperlinks. Is it possible to check whether a cell contains a hyperlink using macros? i.e. is there a piece of code that returns TRUE or FALSE depending on whether a cell has a hyperlink or not.

Any advise (other than don't quit your day job ) or help will be hugely appreciated.

Thanks,
Lochnagae

 

[pboltonchina]

try adjusting this to suit your needs. Try on a copy of your data as it's untested

For Each cell In Rng
    Select Case cell.Hyperlinks.Count
        Case Is > 0
            Select Case IsError(Evaluate(cell.Hyperlinks(1).SubAddress))
                Case 0
                    hyper_st = Replace(Left(cell.Hyperlinks(1).SubAddress, InStr(cell.Hyperlinks(1).SubAddress, "!") - 1), "'", "")
                    
                Case 1
                    EntireRow.Delete
            End Select
    End Select
Next cell

 

[Lochnagar]

Thanks pboltonchina, just was I what after.

Lochnagar