How to Merge Cells in Excel VBA

You can't split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it. For example, you want to split cell A2 into three cells that will appear, side-by-side, under cell A1 (you want to utilize cell A1 as a heading). It is not possible to split cell A2, but you can achieve a similar effect by merging cells A1, B1, and C1 into one, single cell. You then enter your data in cells A2, B2, and C2. These three cells appear as if they are split under one larger cell (A1) that acts as a heading.
----------------------------------------------------------------

1 2 3

Sub Sample1() Range("A1:B5").Merge End Sub

If you run above macro, it will become like this

----------------------------------------------------------------
Now, how to check cells merge or not,

1 2 3 4 5 6 7 8 9 10 11

Sub Sample2() Dim i As Long, buf As String For i = 9 To 22 If Cells(i, 1).MergeCells Then buf = buf & Cells(i, 1).Address(0, 0) & "-->Merged Cell" & vbCrLf Else buf = buf & Cells(i, 1).Address(0, 0) & "-->Not Merge Cell" & vbCrLf End If Next i MsgBox buf End Sub

----------------------------------------------------------------
Merge Area

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Sub Sample3() Dim Target As String, i As Long, buf As String, c Target = InputBox("Seelct Year Target") If Target = "" Then Exit Sub For i = 9 To 22 If Cells(i, 1) = Target Then buf = Target & "(" & Cells(i, 1).Address(0, 0) & ")" & vbCrLf buf = buf & "----------" & vbCrLf For Each c In Cells(i, 1).MergeArea buf = buf & c.Address(0, 0) & vbCrLf Next c MsgBox buf Exit For End If Next i End Sub

----------------------------------------------------------------

1 2 3 4 5 6 7 8 9 10 11

Sub Sample4() Dim buf As String With Range("A30").MergeArea buf = buf & .Rows.Count & " row" & vbCrLf buf = buf & .Columns.Count & " coloum" & vbCrLf buf = buf & .Count & " cells" & vbCrLf buf = buf & .Item(1).Address(0, 0) & " :top left " & vbCrLf buf = buf & .Item(.Count).Address(0, 0) & " :bottom right" End With MsgBox buf End Sub

----------------------------------------------------------------

Link file download