Merge rows across defined set of columns in VBA

Issue

I want to merge a group of rows (keeping all the contents of each row) and then move on to the next column in a defined cell range.

I’m trying to expand this Excel VBA code (https://excelchamps.com/vba/merge/) to a range with more than one column (specifically Range("A25:AC29").

My code gives me a Type Error in the val section.

Sub vba_merge_with_values()
Dim val As String
Dim rng As Range
Dim Cell As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Project Details")
Set rng = ws.Range("A25:A29")
For Each Column In rng.Columns
    For Each Cell In rng.Rows
        val = val & " " & Cell(Row, Column).Value
    Next Cell
    
    With rng.Rows
        .Merge
        .Value = Trim(val)
        .WrapText = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
Next Column
End Sub

Solution

This should work:

Sub vba_merge_with_values()
    Dim Column As Range, rng As Range
    
    Set rng = ThisWorkbook.Worksheets("Project Details").Range("A1:G9")
    For Each Column In rng.Columns
        With Column
            .Cells(1).Value = Join(Application.Transpose(Column.Value), " ")
            Application.DisplayAlerts = False
            .Merge
            Application.DisplayAlerts = True
            .WrapText = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
   Next Column
End Sub

Answered By – Tim Williams

Answer Checked By – Cary Denson (AngularFixing Admin)