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.

Answer 1

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 

Answer 2

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