Delete Hyperlink from Each cell

[yogeshwarv]

Hi borders,

I have an excel sheet in which few cells have hyperlinks. I want to delete hyperlink from all cells with a single command which have hyperlink.

I have more than 20000 records. finding cells with hyperlink and removing them manually is very tiring and time consuming.

It is possible with the help of VB Script.

I am enclosing herewith example sheet for your reference.

Kindly help..

Regards
Yogeshwar

[GTO]

Hi Yogeshwar,

Nice to "meet". Member since 2004 eh? I just joined recently, though longtime 'lurker'/visitor.

Anyways, do you literally mean a vbscript file, or VBA?

Mark

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

.Cells(i, "A").Hyperlinks.Delete
Next i
End With

End Sub

[GTO]

@XLD:
Good morning sir - hope you have a terrific weekend!

@Yogeshwar:
...presuming VBA and in case they aren't all in one column...
Sub Hyperlinks_Del()
Dim hLink As Hyperlink
For Each hLink In Sheet1.UsedRange.Hyperlinks
hLink.Delete
Next
End Sub

Hope this helps,

Mark

[GTO]

Oops! 20,000 + rows?! I'm not sure whether deleting hyperlinks would cause calculation, but I would probably toss in...

Sub Hyperlinks_Del()
Dim hLink As Hyperlink

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each hLink In Sheet1.UsedRange.Hyperlinks
hLink.Delete
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

:doh: Sorry 'bout that,

Mark

[Krishna Kumar]

Hi,

Sub test()
ActiveSheet.Hyperlinks.Delete
End Sub

[GTO]

Where, oh where, to loop? How, oh how, to loop?:dunno

ehhhh.... Nevermind... :doh:

Maybe the plural form should be a clue! Nice one and bravo Krishna!:bow:

Have a great weekend,

Mark

[yogeshwarv]

HI Guys,

Thanx GTO, Thanx Krishna

Both the codes worked. specially Krishna code was very small and performed very quickly. You saved my lots of time from removing hyperlinks manually from each cell. Thanx again buddy.:clap:

Hey,

I have got a file which is basically an address book. Contact records moves to database sheet when entered in the data forum. I need to make this contact file more customized.

I wish when I enter value in First Name, Last Name and Phone No. and transfer the records to database it should check prompt if the these compulsory fields are filled up to avoid mistake else check and prompt if records entered already exist in database else transfer data in the database sheet.

Can anybody please help me in this? It is very complex and puzzled for the person like me. :bug:

I am enclosing herewith the file to make it understand better.

Regards
Yogeshwar