5

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?

Georg Fritzsche
  • 97,545
  • 26
  • 194
  • 236
Oxymoron
  • 1,382
  • 4
  • 20
  • 40

2 Answers2

13
ActiveCell.MergeArea.Count
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
6

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.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • 1
    I'm not sure this is correct. If I merge A1:A4 and put a value in there. Then I put a value in A5, CurrentRegion will return 5 even though there are only 4 merged cells. – Dick Kusleika Nov 04 '09 at 20:57
  • So you didn't want to know the number of cells that were merged? You wanted to know the number of contiguous cells with a value? – Dick Kusleika Nov 09 '09 at 16:55
  • This didn't work for me with MS Excel 2007 on an `.xls` (i.e. not `.xlsx`) sheet. However using `.MergeArea.Count` instead, like Dick suggested, works fine. – Martin Scharrer Jan 29 '14 at 11:23