如何通过Excel VBA将Link Name列字符串设为Hyperlink列超链接的显示名？

Got it, here's a straightforward VBA subroutine that will replace the raw URLs in your Hyperlink column with the corresponding Link Name text, formatted as standard blue underlined hyperlinks.

Sub UpdateHyperlinkDisplayText()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set your target worksheet (replace "Sheet1" with your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the last row with data in the Link Name column (column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each data row (starting at row 2, assuming row 1 is headers)
    For i = 2 To lastRow
        Dim linkDisplayText As String
        Dim targetURL As String
        
        ' Pull values from Link Name (col A) and Hyperlink (col B) columns
        linkDisplayText = ws.Cells(i, "A").Value
        targetURL = ws.Cells(i, "B").Value
        
        ' Clear any existing hyperlinks to avoid duplicates/conflicts
        ws.Cells(i, "B").Hyperlinks.Delete
        
        ' Only add hyperlink if both fields have content
        If targetURL <> "" And linkDisplayText <> "" Then
            ws.Hyperlinks.Add _
                Anchor:=ws.Cells(i, "B"), _
                Address:=targetURL, _
                TextToDisplay:=linkDisplayText
            
            ' Apply Excel's default Hyperlink style (blue, underlined)
            ws.Cells(i, "B").Style = "Hyperlink"
        End If
    Next i
    
    MsgBox "Hyperlinks updated successfully!", vbInformation
End Sub

Key Details & Notes: #

• Column Adjustments: The code assumes Link Name is in column A and Hyperlink is in column B. If your columns are in different positions (e.g., Link Name is column C), update the column letters in ws.Cells(i, "A") and ws.Cells(i, "B") to match your sheet.
• Worksheet Name: Don't forget to change "Sheet1" to the actual name of your worksheet.
• Running the Code: Press Alt+F11 to open the VBA Editor, insert a new module (right-click your workbook in the Project Explorer > Insert > Module), paste this code, then run the UpdateHyperlinkDisplayText subroutine.
• Safety First: Always back up your workbook before running VBA code—better safe than sorry!

内容的提问来源于stack exchange，提问作者sudonym