VBA code to remove hyperlink from row

[jaxneng]

Hi,

I need help to create a command button to remove hyperlinks from rows that have a 'date' on column E.

note:
the cells on column 'E' are linked to cell K4 on other files. if cell K4 on those files is blank the cells in column E will show as 1/0/00.

the logic I need is the following:
command button 'remove hyperlinks' is clicked
(a) if a cell in column E equals 1/0/00 then leave hyperlinks in that row intact
(b) if a cell in column E is not equal to 1/0/00 then copy and paste (as values) all values in that row (I want to remove the hyperlink but at the same time keep the values).
the command is to affect columns E and on (columns A to D are to remain intact).
thank you in advance for your help!

 

[Osvaldo Palmeiro]

Hi.
I'm not sure if you want to change to it's value only on column "E" or to their values on range "A:E", so, uncomment on the code below the row to suit.

Sub Test()
 Dim rngDT As Range
  For Each rngDT In Range("E1:E" & Range("E1").End(xlDown).Row)
   If rngDT.Value > 0 Then
    'rngDT.Value = rngDT.Value
    'Cells(rngDT.Row, 1).Resize(, 5).Value = Cells(rngDT.Row, 1).Resize(, 5).Value
   End If
  Next rngDT
End Sub

 

[jaxneng]

thanks Osvaldo, I want to change to its value on columns F to Q. I'm not sure on how to modify your code to do this. thanks again for the help.

 

[Osvaldo Palmeiro]

Hi.
Okay, so try this instead

Sub Test()
 Dim rngDT As Range
  For Each rngDT In Range("E1:E" & Range("E1").End(xlDown).Row)
   If rngDT.Value > 0 Then
    Cells(rngDT.Row, "F").Resize(, 12).Value = Cells(rngDT.Row, "F").Resize(, 12).Value
   End If
  Next rngDT
End Sub

 

[jaxneng]

no luck. I copied/pated your code into a command button but it does not do anything


Private Sub CommandButton21_Click()
Dim rngDT As Range
For Each rngDT In Range("E1:E" & Range("E1").End(xlDown).Row)
If rngDT.Value > 0 Then
Cells(rngDT.Row, "F").Resize(, 12).Value = Cells(rngDT.Row, "F").Resize(, 12).Value
End If
Next rngDT
End Sub