Broken Hyperlinks (lots in one sheet)

**NeedsExcelHelp** · 10-31-2011, 04:38 PM

I have a sheet (attached) with roughly 22,400 hyperlinks that go to reviews on products. The problem is the person that created this sheet didn't have a plan for products that are removed from the web. So, needless to say there lots of links in here that lead to 404 web errors as the pages no longer exist. Is there a Macro or Formula I can run to have it ping all of these hyperlinks for a 404 or other error if the webpage no longer exists? I deleted all but about 250 of the links so you could see a subset of what I am working with in the attachment.

Thanks,
Jason

**snb** · 10-31-2011, 04:49 PM

Sub snb()
 On Error Resume Next

 For Each hp In Hyperlinks
  ThisWorkbook.FollowHyperlink hp.Address
  If Err.Number <> 0 Then hp.Delete
  Err.Clear
 Next
End Sub

**NeedsExcelHelp** · 10-31-2011, 04:55 PM

Originally Posted by snb

Sub snb()
 On Error Resume Next

 For Each hp In Hyperlinks
  ThisWorkbook.FollowHyperlink hp.Address
  If Err.Number <> 0 Then hp.Delete
  Err.Clear
 Next
End Sub

I added this as a module under VBA. I ran it, but it didn't seem to do anything. I suspect it was supposed to delete an invalid URL, but take lin 89 for example - I get a 404 error, yet it didn't delete it. Thoughts?

**snb** · 10-31-2011, 05:07 PM

Please do not quote (see the forum rules ).

You'd better put this into the macromodule of the sheet that contains the hyperlinks.

**NeedsExcelHelp** · 11-01-2011, 11:28 AM

snb. Thank you for the help. I did create it as a macro within the spreadsheet I am working, but it still seems to be unresponsive. Any ideas what I might be doing wrong?

**snb** · 11-01-2011, 12:24 PM

I hope you put it into the right sheet module.
Well, you have to run the code (alt-F8)

After that the only thing that is noticeble in the sheet is that certain links lack the understrike and have another colour.

You can post a sample workbook (containing 1 sheet and only a few rows containing hyperlinks and the macro).

**NeedsExcelHelp** · 11-01-2011, 02:22 PM

I was definitely running the code, and as I am pretty new to Macros/VBA I suspect I am simply inserting it wrong. I have attached a sheet with the Macro as you requested and deleted remaining sheets.