Determining the size of a merged area

Question

Right, before we get off about merged cells, I hate them too, but I've to deal with them anyway. I cannot change anything about that now or in the future. As much as I would like to ;)

Say I have some merged cells, I need to determine the amount of cells it spans. Say A1:A4 are merged, then I need to have the number of merged cells, 4, returned. Is there any way to accomplish this?

Answer 1

You can use

Dim r As range
Dim i As Integer
    Set r = range("A1")
    i = r.CurrentRegion.Count

This will give A1:A4 as 4, A1:B4 as 8.

Answer 2

ActiveCell.MergeArea.Count