Excel VBA merging cells in a function

Question

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?

Why are you merging the cells you want to concatenate before you've done the concatenation? — Kevin Pope
I'm concatenating a bunch of rows into one cell, the text is long and not visible so I'm trying to merge the same number of rows over a column where the concatenated string is being written into so its fully visible — keeg
Instead of merging, rows, can't you just resize the row? Perhaps I'm not understanding what your aim is here. — Marc
Also, you'll want to do your concatting BEFORE you merge the cells and lose data. — Marc
I also believe you need to make structural changes to a sheet (ex. .Merge) from within a Sub instead of a Function. Reasons being the function could be called from the sheet and merge itself out of existence, and the Function will run with every recalculation cycle. — Kevin Pope

brettdj brettdj · Accepted Answer · 2012-11-13T22:22:32

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 merging cells in a function

1 Answers