Detect merged cells in VBA Excel with MergeArea

Question

I m having quite an issue with this one - I have to detect horizontally and vertically merged cells from an excel table  I have to store the first cell coords  and the lenght of the merged area  I iterate through the table with two for-cycles  line by line   How can I use MergeArea property to detect the merged and non-merged areas  If the cell is not merged  it should probably return empty range  however  this    If currentRange Is Nothing Then   is not working at all  Any ideas  Thanks a lot

User · Accepted Answer

There are several helpful bits of code for this.

Place your cursor in a merged cell and ask these questions in the Immidiate Window:

Is the activecell a merged cell?

? Activecell.Mergecells
 True

How many cells are merged?

? Activecell.MergeArea.Cells.Count
 2

How many columns are merged?

? Activecell.MergeArea.Columns.Count
 2

How many rows are merged?

? Activecell.MergeArea.Rows.Count
  1

What's the merged range address?

? activecell.MergeArea.Address
  $F$2:$F$3

User · Answer

While working with selected cells as shown by  tbur can be useful  it s also not the only option available   You can use Range   like so   If Worksheets  Sheet1   Range  A1   MergeCells Then   Do something Else   Do something else End If   Or   If Worksheets  Sheet1   Range  A1 C1   MergeCells Then   Do something Else   Do something else End If   Alternately  you can use Cells     If Worksheets  Sheet1   Cells 1  1  MergeCells Then   Do something Else   Do something else End If

[vba] Detect merged cells in VBA Excel with MergeArea

Examples related to vba

Examples related to excel

Examples related to merge