删除超链接，保留公式和格式

问题描述：

我对Excel不太好，但我会尝试解释我的问题。不知何故，通过计时器创建了一个excel，并以某种方式在整个表格中传播了100多个不可见的超链接。我试图找到一种方法从A1复制：k50删除所有超链接，但保留公式，值和格式。我在网上发现了这个代码，并且我尝试添加HR.PasteSpecial xlPasteFormulas，但是这似乎不起作用。任何想法/想法将不胜感激。删除超链接，保留公式和格式

Sub RemoveHlinks() 
'Remove hyperlinks from selected cells without 
'removing the cell formatting. 
Dim Hlink  As Hyperlink 
Dim HR   As Range 
Dim Temp  As Range 
Dim MaxCol  As Integer 

With ActiveSheet.UsedRange 
    MaxCol = .Column + .Columns.Count 
End With 

Set Temp = Cells(1, MaxCol) 

For Each Hlink In Selection.Hyperlinks 
Set HR = Hlink.Range 
HR.Copy Destination:=Temp 
HR.ClearContents 
Set Temp = Temp.Resize(HR.Rows.Count, HR.Columns.Count) 
Temp.Copy 
HR.PasteSpecial xlPasteFormats 
HR.PasteSpecial xlPasteValues 
Temp.Clear 
Next Hlink 

End Sub

答

（编辑）

我相信你将不得不每个属性中的每个单元格复制（希望没有合并的，这会导致额外的麻烦），然后删除它的超级链接，之后恢复propertyes。

您可以录制宏发现所有的属性，下面是一些例子字体和内饰。为了发现其他属性，您可能需要这样做，您将不得不开始录制宏，选择某个单元格，手动更改该属性，停止录制以及在生成的代码中查看该属性是什么。

Sub Macro1() 
    ' 
    ' Macro1 Macro 
    ' 


     Dim Cell As Range 
     Dim SelectedRange As Range 

     Set SelectedRange = ActiveSheet.Range("A1:K50") 

     Dim Rows As Integer 
     Dim Columns As Integer 
     Dim i As Integer 
     Dim j As Integer 


     Rows = SelectedRange.Rows.Count 
     Columns = SelectedRange.Columns.Count 

     For i = 1 To Rows 
      For j = 1 To Columns 
       Set Cell = SelectedRange.Cells(i, j) 
       Call ClearHyperlinks(Cell) 
      Next 
     Next 

    End Sub 


    Sub ClearHyperlinks(Cell As Range) 
     '''''''''' Font Properties'''''''''''''' 

     Dim fName As Variant 
     Dim fFontStyle As Variant 
     Dim fSize As Variant 
     Dim fStrikethrough As Variant 
     Dim fSuperscript As Variant 
     Dim fSubscript As Variant 
     Dim fOutlineFont As Variant 
     Dim fShadow As Variant 
     Dim fUnderline As Variant 
     Dim fThemeColor As Variant 
     Dim fTintAndShade As Variant 
     Dim fThemeFont As Variant 

     With Cell.Font 
      fName = .Name 
      fFontStyle = .FontStyle 
      fSize = .Size 
      fStrikethrough = .Strikethrough 
      fSuperscript = .Superscript 
      fSubscript = .Subscript 
      fOutlineFont = .OutlineFont 
      fShadow = .Shadow 
      fUnderline = .Underline 
      fThemeColor = .ThemeColor 
      fTintAndShade = .TintAndShade 
      fThemeFont = .ThemeFont 
     End With 



     ''''''''''Interior Properties'''''''''''''' 

     Dim iPattern As Variant 
     Dim iPatternColorIndex As Variant 
     Dim iThemeColor As Variant 
     Dim iTintAndShade As Variant 
     Dim iPatternTintAndShade As Variant 

     With Cell.Interior 
      iPattern = .Pattern 
      iPatternColorIndex = .PatternColorIndex 
      iThemeColor = .ThemeColor 
      iTintAndShade = .TintAndShade 
      iPatternTintAndShade = .PatternTintAndShade 
     End With 


     ''''''''''''' Number Format ''''''''' 
     Dim NumberFormat As Variant 
     NumberFormat = Cell.NumberFormat 

     '''''''''''''' Delete Hyeperlinks 
     Cell.Hyperlinks.Delete 



     ''''''''''''''''''Restore properties''''''''''''''' 

     Cell.NumberFormat = NumberFormat 


     With Cell.Font 
      .Name = fName 
      .FontStyle = fFontStyle 
      .Size = fSize 
      .Strikethrough = fStrikethrough 
      .Superscript = fSuperscript 
      .Subscript = fSubscript 
      .OutlineFont = fOutlineFont 
      .Shadow = fShadow 
      .Underline = fUnderline 
      .ThemeColor = fThemeColor 
      .TintAndShade = fTintAndShade 
      .ThemeFont = fThemeFont 
     End With 

     With Cell.Interior 
      .Pattern = iPattern 
      .PatternColorIndex = iPatternColorIndex 
      .ThemeColor = iThemeColor 
      .TintAndShade = iTintAndShade 
      .PatternTintAndShade = iPatternTintAndShade 
     End With 


    End Sub

（原件）你可以简单地手动或自动（包括超链接）复制一切。而在粘贴的那些东西的新表，只是删除使用超链接：

Selection.Hyperlinks.Delete

当使用selection.hyperlinks.delete它删除单元格（大胆，背景颜色等），这就是为什么这是行不通的格式。 – John 2013-03-19 20:19:31

嗯，这个可以工作，但可能会导致合并单元格的问题或超时链接一次占用很多单元格的问题。 – 2013-03-19 21:29:10

（我发誓当我发布代码时缩进了代码） – 2013-03-19 21:29:43

答

我也想知道为什么，但在通过这个代码实际工作行阅读，你需要做的就是按照提到的注意事项：

“从选择细胞删除链接，而不 ”删除单元格格式。

即高亮显示/选择列（或细胞）和运行该代码

瞧，超链接去除，而保留格式。

丹尼斯

删除超链接，保留公式和格式

相关推荐