Automation error - Element not found

[Caddyman]

Hi

i use the following bit of code to delete Hyperlinks when I cut and paste data from the internet to excel.

[vba]Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub[/vba]

It works very well most of the time, BUT when I get a large amount of data, last example was just over 2000 lines with a single hyprelink in each row, I get the following error.

Run-time error '-2147319765 (8002802b)':

Automation error
Element not found.

When i debug it highlights ActiveSheet.Hyperlinks.Delete


Any help you can provide is much appreciated.

Thanx
Grant

[Jan Karel Pieterse]

Maybe you need to do them one-by-one (not tested):

Dim lCount as long
'Step in reverse so Excel doesn't get confused
For lCount=Hyperlinks.Count To 0 Step -1
Hyperlinks(lCount).Delete
Next

[Bob Phillips]

Small correc tion to Jan Karel's code

[vba]

Dim lCount As Long
'Step in reverse so Excel doesn't get confused
For lCount = ActiveSheet.Hyperlinks.Count To 1 Step -1
ActiveSheet.Hyperlinks(lCount).Delete
Next
[/vba]

[Jan Karel Pieterse]

Thanks xld!

[Caddyman]

Tried this code as suggested
[vba]Sub RemoveHyperLinks2()
Dim lCount As Long
'Step in reverse so Excel doesn't get confused
For lCount = ActiveSheet.Hyperlinks.Count To 1 Step -1
ActiveSheet.Hyperlinks(lCount).Delete
Next
End Sub
[/vba]
But got the same error, this time the debugger stops on this line
[vba] For lCount = ActiveSheet.Hyperlinks.Count To 1 Step -1[/vba]
Is there a maximum number of objects that Activesheet can handle?? I ask this because the following also gets the same error message on a large sheet, but work well on small numbers.
[vba]Sub RemovePictures()
'Selects all Pictures on the active sheet and deletes them
Dim DrObj
Dim Pict
Set DrObj = ActiveSheet.DrawingObjects
For Each Pict In DrObj
DrObj.Delete
Next
End Sub[/vba][vba]Sub RemomveControls()
Dim obj As OLEObject
'Selects all ActiveX controls on the active sheet and deletes them
For Each obj In ActiveSheet.OLEObjects
obj.Delete
Next obj
End Sub[/vba]

[Bob Phillips]

If you hover this bit of code

ActiveSheet.Hyperlinks.Count

what do you get?

[Caddyman]

If you hover this bit of code

ActiveSheet.Hyperlinks.Count

what do you get?

Nothing???? the only part thats show up when i hover is lcount=0

[Jan Karel Pieterse]

What about thisone:

Sub RemoveLinksTheHardWay()
    On Error Resume Next
    Do While Err.Number = 0
        ActiveSheet.Hyperlinks(1).Delete
    Loop
End Sub

If this one fails too, try if your problem goes away on a freshly inserted worksheet.