Clearing Hyperlink formating from undefined # of cells in a column

[Phia]

I pull metrics on a weekly basis, which include the hyperlink format in the
worksheet. I need to remove the hyperlink in order to remove the headache of
someone reading the report and accidently clicking on the hyperlink and going
no-where.

Right now I manually select each cell in the same column and right mouse
click to remove each hyperlink. If my report is over 100+ rows in length,
this can get tedious and take a long time. So, how do I create a vb
program/macro that will allow me to select the first cell in the column,
remove the hyperlink format, then select the next cell, and continue clearing
the format until there is no more entries in the column?

I've tried to use Range, but there doesn't appear to be a clear format
parameter available. Any help is very much appreciated!

 

[Derek P.]

This is a bit overkill but the function allows you to specify a single sheet
to remove the hyperlinks from, or defaults to removing all links in the
workbook. The function also returns the total number of links that it removed
should you have any reason for it.

'Remove all hyperlinks in a workbook or specific sheet
Private Function iRemoveHyperlinks(Optional ByVal ssheet_name As String =
"Workbook") As Integer
Dim vworksheet As Variant
Dim vlink As Variant
Dim itotal_links_Removed As Integer
itotal_links_Removed = 0
ssheet_name = UCase(ssheet_name)
For Each vworksheet In ThisWorkbook.Sheets
'If a single sheet is defined, remove links only from that sheet.
'otherwise remove links from entire workbook
If ssheet_name <> "WORKBOOK" Then
If UCase(vworksheet.Name) = ssheet_name Then
itotal_links_Removed = itotal_links_Removed +
vworksheet.Cells.Hyperlinks.Count
vworksheet.Cells.Hyperlinks.Delete
End If
Else
itotal_links_Removed = itotal_links_Removed +
vworksheet.Cells.Hyperlinks.Count
vworksheet.Cells.Hyperlinks.Delete
End If
Next vworksheet
iRemoveHyperlinks = itotal_links_Removed
End Function

Happy metrix'n

Derek P.

 

[Dave Peterson]

You could use a macro that removes the hyperlinks from the selection.

Option Explicit
Sub testme()
With Selection
.Hyperlinks.Delete
.Style = "Normal"
End With
End Sub

Select the range to fix first. (Selecting all the cells is ok, too.)

 

[Gord Dibben]

Are the hyperlinks created using the =HYPERLINK function?

If so, the following won't work

Sub DelAllHyperlinks()
ActiveSheet.HyperLinks.Delete
End Sub


Gord Dibben MS Excel MVP