Merged cell is very common while we need to summarize some columns or rows in Excel. In order to automatically format the table, handling merged cell with VBA is necessary.
Let's see some examples!
Let's see some examples!
Create a merged cell
ActiveSheet.Range("B2:C4").Merge
Check if a range is merged cell
Debug.Print ActiveSheet.Range("B2").MergeCells
'True
Debug.Print ActiveSheet.Range("C3").MergeCells
'True
Debug.Print ActiveSheet.Range("B4").MergeCells
'True
Debug.Print ActiveSheet.Range("D2").MergeCells
'False
Get address/column/row of merged cell
Dim objRangeToMerge As Range
Set objRangeToMerge = ActiveSheet.Range("B2:C4")
objRangeToMerge.Merge
Dim objMergedCell As Range
Set objMergedCell = objRangeToMerge.Cells(1)
Debug.Print objMergedCell.Address
'$B$2
Debug.Print objMergedCell.Column
'2
Debug.Print objMergedCell.Row
'2
Get merged columns/rows
If ActiveSheet.Range("B2").MergeCells Then
Dim objMergedCell As Range
Set objMergedCell = ActiveSheet.Range("B2").MergeArea
Debug.Print objMergedCell.Columns.Count
'2
Debug.Print objMergedCell.Rows.Count
'3
End If
Comments
Post a Comment