Size of merged cells

[YuraSV]

Hi!


Help me please. I`m writing script in Excell and need to know sizes of merged cell? How can I do it?



With best regards,
Yura.

[RobDog888]

Here is how you can do it.

Public Sub MergeLen()
    'A2:B2 = Merged cell with contents of "test"
    MsgBox Len(Sheet1.Cells(2, 1)) '=4
End Sub

[YuraSV]

Hmmm.

Your example describes how to get sizes in pixels.

I mean another sizes. Let`s suppose we have merged cell. I need to know how much cells is included in width of merged cell and how much in height.

[RobDog888]

Thought you meant character length.
So you want the size in cells?

[RobDog888]

This will get the character length of the merged cell contents and
the cell range of the merged cell.

Public Sub MergeLen()
    'A2:B2 = Merged cell with contents of "test"
    MsgBox Len(Sheet1.Cells(2, 1)) '=4
    MsgBox Sheet1.Range("A2").MergeArea.Address
End Sub

[YuraSV]

One more question (I hope it will be the last)

Is there any function that will return number of columns and rows? Something like

Sheet1.Range("A2").MergeArea.ColumnCount


With best regards,
Yura.


P. S. Thanks a lot for your help

[RobDog888]

In my previous post, you can see that the code gets the range
of the merged cells. From that you can determine the number of
columns and rows that the range covers. Ex. $A$2:$B$2 you can
tell that the column count is from A to B so 2 columns and 2 to 2
so 1 row. If you want to test if the cell is a merged cell then use
the .MergeCells property which will be true if its a merged cell.

HTH

[YuraSV]

So I have parse string that MergedArea() returned?

[RobDog888]

Yes, that is correct.