Dave:
When I tried your testme3 code, it still removed the hyperlink from all
except the last one in the column, but your code maintained the blue color of
the cells it removed the hyperlinks from (except the first one - G1), as if
they were still hyperlinks. Thank you so much for your persistence in trying
to make this work. Your response told me that it wasn't as easy of a task as
I thought it would / should be, which decreased my frustration level.
okaizawa provided a short piece of code which seems to consistently work.
I'm thrilled to have this problem resolved. Thanks again!
Elizabeth

"Dave Peterson" wrote:

I tried this:

I put a hyperlink in A5 (just typed www.microsoft.com)
I autofilled (by dragging) into A4:A1

Then ran this:
Option Explicit
Sub testme()
Range("a1").Hyperlinks.Delete
End Sub

And it duplicated the OP's problem.

Before I did the deletion,
?activesheet.hyperlinks.count
returned 2 (instead of one each for a1 to a5)

(sigh: Hyperlinks are strange beasts.)

I thought that if I could break that "group" of links, then I could delete the
link in A1:

Option Explicit
Sub testme()
With Range("a1")
If .Hyperlinks.Count 0 Then
.Hyperlinks.Add anchor:=.Cells, Address:="www.dummy.com"
End If
.Hyperlinks.Delete
End With
End Sub

but it didn't work.

But this did:

Option Explicit
Sub testme2()
Dim myFormula As String
With Range("a1")
myFormula = .Formula
.Clear
.Formula = myFormula
End With
End Sub

To the original poster: You'll have to keep track of everything you
want--formatting (numberformat, font, boldness, underlining, borders) and then
reapply them after you clear the cell.

Them hyperlink thingies is ugly!


Ahhhh. This worked better.

Option Explicit
Sub testme3()
Dim dummyCell As Range
Set dummyCell = ActiveSheet.Cells _
.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With dummyCell
Range("a1").Copy _
Destination:=.Cells
.Hyperlinks.Delete
.Copy _
Destination:=Range("a1")
.Clear
End With
End Sub

It copies the cell to clear to the cell to the bottom right of the last used
cell. Does the work and pastes it back.

Maybe the idea will work ok for you, too.

Elizabeth wrote:

Tom:
Thank you for your suggestion. I tried it, but get the same result as my
code (it initially works, but on subsequent tries it deletes all hyperlinks
except the last one in the column). But thanks again for giving it a shot.
Elizabeth

"Tom Ogilvy" wrote:

Try

Sub Hyperlink_Remove()
Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks(1).
Delete
End Sub

--
Regards,
Tom Ogilvy


"Elizabeth" wrote in message
...
i am trying to write a macro to delete the hyperlink in G1; i want all
other
hyperlinks in column G to remain functional. [i daily copy & paste info
into
this column but don't want the title's hyperlink to be used.] when i use
the
following code, it works the first time. but while testing (i'm new at
vba),
when i copy a hyperlink from G2 or G10 etc., up to G1 & run my macro
again,
it usually removes the hyperlink from all except the last one in the
column
(i.e., it removes G1:G9 hyperlinks & leaves G10's). why is this
happening?

Sub Hyperlink_Remove()

Workbooks("Fiduciaries.xls").Worksheets("Sheet4"). Range("G1").Hyperlinks.Del
ete
End Sub

thank you for any suggestions.
elizabeth




--

Dave Peterson