Excel VBA在一个功能中合并单元格 [英] Excel VBA merging cells in a 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 
 
设置CellStart = Worksheets（1）.Cells.Range（B& CellRef）
设置CellEnd = Worksheets（1）.Cells.Range（B& CellRefEnd）
 
 Col = CellStart.Column 
 StartRow = CellStart.Row 
 EndRow = CellEnd.Row 
 
带范围（CellStart，CellEnd）
 .Merge 
 .WrapText = True 
结束
 
 Concat =
 
对于LoopVar = StartRow To EndRow 
 Concat = Concat&单元格（LoopVar，Col）.Value 
如果LoopVar<> EndRow然后Concat = Concat&分隔符
下一个LoopVar 
 
 GetSkills = Concat 
 
结束函数
  

其中我正在尝试合并单元格，当我运行该函数时，我得到一个提示：

选择包含多个数据值。合并成一次单元格
将保留左上角的最多数据

我单击确定，Excel崩溃，重新启动和再次提示对话框。有没有另外一种方式来合并使用VBA的单元格块？

解决方案

通常合并单元格不是一个好主意。这是一种化妆品格式化方法，可能会导致VBA代码的破坏。

免责声明，一些建议

• 使用Sub而不是功能，您希望使用更改范围


• 使用 Application.DisplayAlerts 抑制合并单元消息


• 可以显着减少代码

代码

  Sub Test（）
调用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）
设置CellEnd = Worksheets（1）。 Cells.Range（B& CellRefEnd）
 
 Concat = Join（Application.Transpose（Range（CellStart，CellEnd）），Delimiter）
 
带范围（CellStart， CellEnd）
 .Merge 
 .WrapText = True 
 .Value = Concat 
结束
 Application.DisplayAlerts = True 
 End Sub 
  

I wrote a crude function to select and concatenate cells based on a range.

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

Within it I'm trying to merge the cells, when I run the function I get a prompt saying:

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

I click OK and Excel crashes, restarts, and prompts the dialog again. Is there another way to merge a block of cells using VBA?

解决方案

Generally merging cells is not a good idea. It is a cosmetic formatting approach that can cause havoc with VBA code.

Disclaimers aside, a few suggestions

• use a Sub rather than a Function given you want to work with altering the range
• use Application.DisplayAlerts to suppress the merge cells message
• you can cut down the code significantly

code

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

这篇关于Excel VBA在一个功能中合并单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！