PDA

View Full Version : Delete Hyperlink from Each cell



yogeshwarv
11-08-2008, 03:11 AM
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
11-08-2008, 03:31 AM
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
11-08-2008, 03:50 AM
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
11-08-2008, 04:22 AM
@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
11-08-2008, 04:33 AM
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
11-08-2008, 06:12 AM
Hi,

Sub test()
ActiveSheet.Hyperlinks.Delete
End Sub

GTO
11-08-2008, 08:28 AM
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
11-13-2008, 07:06 AM
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