CODEWITHSUNDEEP
vbaexcelmerge
zirael
zirael

Reputation: 413

Detect merged cells in VBA Excel with MergeArea

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.

Upvotes: 41

Views: 159553

Answers (2)

David Metcalfe
David Metcalfe

Reputation: 2412

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

Upvotes: 8

tbur
tbur

Reputation: 2454

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

Upvotes: 130

Related Questions