• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to delete multiple hyperlinks throughout multiple worksheets.

mrvic90631

New Member
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.

Code:
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
 

Attachments

  • Testfile.xlsm
    20.7 KB · Views: 3
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.

Code:
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
 
A good code no needs Select or Activate !

Just respect TBTO rule (Think, But Think Object !) :​
Code:
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 !
 
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 !
 
@Marc L
As always, very efficient coding.
I've got to really sit down and properly understand Object model one of these days.
 
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 / …
 
Back
Top