Sizing a merged area

Question

That's right, before we get together with the merged cells, I hate them too, but I still have to deal with them. I can’t change anything about it now or in the future. As far as I would like;)

Say I have merged cells, I need to determine the number of cells that it spans. Say A1: A4 is merged, then I need the number of merged cells to be returned 4. Is there a way to do 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