如何用Excel VBA创建按钮实现指定列批量下划线分隔合并

Got it, let's walk through exactly how to set up this clickable button with VBA to merge your row data—perfect for when your data changes frequently and formulas just won't cut it.

First, make sure the Developer tab is visible (it's hidden by default for most users):

• Right-click any ribbon tab > Select Customize the Ribbon
• Check the box next to Developer in the right pane > Click OK

Now add the button:

• Go to the Developer tab > Click Insert > Under Form Controls, pick the Button (Form Control)
• Drag a rectangle on your worksheet where you want the button to sit (placing it near your data range makes it easy to access)
• When the Assign Macro window pops up, click New—this will open the VBA editor with a blank macro template.

Replace the empty macro code with this tailored script:

Sub MergeRowDataWithUnderscores()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim mergedText As String
    Dim col As Integer
    
    ' Set your target worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Define the last row to process (matches your 2:100 range)
    lastRow = 100
    
    ' Loop through each row from 2 to 100
    For i = 2 To lastRow
        mergedText = ""
        ' Loop through columns B to J (column numbers 2 to 10)
        For col = 2 To 10
            ' Skip empty cells to avoid extra underscores
            If ws.Cells(i, col).Value <> "" Then
                ' Add an underscore separator if we already have text
                If mergedText <> "" Then
                    mergedText = mergedText & "_"
                End If
                mergedText = mergedText & ws.Cells(i, col).Value
            End If
        Next col
        ' Drop the merged result into column A of the same row
        ws.Cells(i, 1).Value = mergedText
    Next i
    
    ' Optional confirmation message
    MsgBox "Row data merged successfully!", vbInformation
End Sub

Here's what this code handles specifically for your needs:

• It targets your exact range (rows 2-100, columns B-J)
• Skips empty cells so you don't end up with messy double underscores
• Merges non-empty values with underscores as separators
• Overwrites old merged values in column A every time you run it (ideal for frequent data updates)
• Includes a quick confirmation pop-up so you know it's done

• Go back to your worksheet, right-click the button > Select Edit Text to rename it (something like "Merge Row Data" is clear)
• Enter test data in columns B-J, then click the button—you'll see column A populate with the merged text instantly
• If your data range ever changes (e.g., you need to go to row 200), just open the VBA editor (Developer tab > Visual Basic), find the lastRow = 100 line, and adjust the number
• Want a different separator? Swap out the "_" in the code with whatever you need (like "-" or "|")

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