Excel VBA 在函数中合并单元格

Question

我编写了一个粗略的函数来根据范围选择和连接单元格。

Function GetSkills(CellRef As String, CellRefEnd As String, Delimiter As String)

    Dim CellStart As Range
    Dim CellEnd As Range
    Dim LoopVar As Long
    Dim StartRow As Long
    Dim EndRow As Long
    Dim Concat As String
    Dim Col As Long

    Set CellStart = Worksheets(1).Cells.Range("B" & CellRef)
    Set CellEnd = Worksheets(1).Cells.Range("B" & CellRefEnd)

    Col = CellStart.Column
    StartRow = CellStart.Row
    EndRow = CellEnd.Row

    With Range(CellStart, CellEnd)
        .Merge
        .WrapText = True
    End With

    Concat = ""

    For LoopVar = StartRow To EndRow
        Concat = Concat & Cells(LoopVar, Col).Value
        If LoopVar <> EndRow Then Concat = Concat & Delimiter & " "
    Next LoopVar

    GetSkills = Concat

End Function

在其中我试图合并单元格，当我运行该函数时，我收到一条提示:

The selection contains multiple data values. Merging into once cell will keep the upper-left most data only

我单击“确定”，Excel 崩溃、重新启动并再次提示该对话框。是否有其他方法使用 VBA 合并单元格 block ？

Answer 1

一般来说，合并单元格并不是一个好主意。这是一种修饰格式方法，可能会对 VBA 代码造成严重破坏。

除了免责声明外，还有一些建议

• 如果您想要更改范围，请使用 Sub 而不是 Function
• 使用 Application.DisplayAlerts 抑制合并单元格消息
• 您可以显着减少代码

代码

Sub Test()
Call GetSkills(2, 4, ",")
End Sub

Sub GetSkills(CellRef As String, CellRefEnd As String, Delimiter As String)
Dim CellStart As Range
Dim CellEnd As Range
Dim Concat As String

Application.DisplayAlerts = False
Set CellStart = Worksheets(1).Cells.Range("B" & CellRef)
Set CellEnd = Worksheets(1).Cells.Range("B" & CellRefEnd)

Concat = Join(Application.Transpose(Range(CellStart, CellEnd)), Delimiter)

With Range(CellStart, CellEnd)
    .Merge
    .WrapText = True
    .Value = Concat
End With
Application.DisplayAlerts = True
End Sub