VBA code to delete multiple hyperlinks throughout multiple worksheets.

[mrvic90631]

I have a Excel sheet with Thousands of hyperlinks and i need to be able to send it to customers as a excel file with no hyperlinks.
I have this code and it works just fine and gets the job done but i keep getting a error that states "Object Variable or With block variable not set." what am i doing wrong? test file uploaded.

Sub DeleteHyperlinks()
Dim sht As Worksheet
  For Each sht In ThisWorkbook.Worksheets
    Cells.Select
    Range("A1").Activate
    Selection.Hyperlinks.Delete
    ActiveSheet.Next.Select
  Next sht
End Sub

 

[Chihiro]

That's because when the code runs for last sheet, it cannot find ".Next".

But since the code has gone through all the sheets, your required operation has been completed.

You can do something like below to avoid error message.

Sub DeleteHyperlinks()
Dim sht As Worksheet
ThisWorkbook.Sheets(1).Select
Do
  For Each sht In ThisWorkbook.Worksheets
    Cells.Select
    Range("A1").Activate
    Selection.Hyperlinks.Delete
    If ActiveSheet.Index < Sheets.Count Then
        ActiveSheet.Next.Select
    Else
        Exit Do
    End If
  Next sht
Loop
End Sub

 

[Marc L]

A good code no needs Select or Activate !

Just respect TBTO rule (Think, But Think Object !) :​

Sub DeleteHyperlinks()
         Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
             sht.Hyperlinks.Delete
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[mrvic90631]

Thanks Guys, Marc I'm sorta new to VBA so i guess i was overthinking it.

 

[Marc L]

Just ask yourself good questions !

What I really need to do ?
I just need to delete hyperlinks of each worksheet of the workbook.

What are the Excel objects involved in my need ?
A Worksheet variable named sht used in a For Each block scanning
within the workbook (ThisWorkbook) its Worksheets collection
and the worksheet Hyperlinks collection : code design is done !
​

 

[Chihiro]

@Marc L
As always, very efficient coding.
I've got to really sit down and properly understand Object model one of these days.

 

[Marc L]

Thanks !

Chihiro, you already know that from your Excel skills !
All you operate within Excel are this object model :
Application - Workbook - Worksheet - Cell / Range / Column / Row /
AutoFilter / Hyperlinks / …
​