[Excel VBA] How to Check if Cells are Merged and Get the Merged Range | Using MergeCells and MergeArea

Excel tables often contain merged cells. When manipulating these with VBA, it is crucial to correctly determine “whether a cell is merged” and “how to retrieve the entire merged range.”

In this article, I will explain how to check for merged cells and how to get the merged area using VBA.

Code Example: Detecting Merged Cells and Getting the Range

Dim checkRange As Range
Set checkRange = Range("E5:F6")

If checkRange.MergeCells = True Then
    ' Note: We use .Cells(1) to ensure we reference the top-left cell of the range
    MsgBox "Merged Range: " & checkRange.Cells(1).MergeArea.Address
Else
    MsgBox "This cell is not merged."
End If

Explanation of the Code

1. Determine if merged using the MergeCells property

If checkRange.MergeCells = True Then

If this returns True, it means the specified range contains merged cells.

2. Get the merged range using the MergeArea property

checkRange.Cells(1).MergeArea

If the cell is merged, this returns the Range object representing the entire merged area (based on the first cell).

3. Display the range using the Address property

Using .Address, we retrieve the address string (e.g., $E$5:$F$6) of the merged range and display it in a message box.

Execution Examples and Verification

Example 1: If cells E5:F6 are merged

Merged Range: $E$5:$F$6

Example 2: If cells are not merged

This cell is not merged.

Application: How to Unmerge Cells?

If you want to automatically unmerge cells after detecting them, you can write the code as follows:

If checkRange.MergeCells = True Then
    checkRange.UnMerge
End If

Summary

Handling merged cells in VBA is common in various scenarios, such as printing formatting, forms, and automatic processing of tabular data. Mastering MergeCells and MergeArea allows for flexible and safe processing.