AutoFitMergedCellRowHeight Limitation

[Charlie W]

Thought I posted this question yesterday, but can't find it today. Please
excuse me if this becomes a duplicate post.

I'm trying to autofit text into a single-row merged cell. I wrote a macro
remarkably similar to the one referenced in this question. Both macros work
_most_ of the time. But, it appears that when the length of the text exceeds
some 'magic' value, neither macro works correctly. The problem seems to
occur when the length of the text is more that 400-500 characters. In those
cases, the merged cell gets resized, but not high enough to make all the text
visible.

Even if I place the same text into a single non-merged cell, the behavior
(and the flaw) is the same. The cell is resized, but the last few wrapped
lines of text are cut off. If I manually resize the cell, I can see all the
text. But if I then use the AutoFit function, the cell shrinks back down to
hide the last few lines of text.

Any ideas?

 

[JLGWhiz]

This is from the xl2003 specifications and limits:

Length of cell contents: (text) 32,767 characters. Only 1,024 display in a
cell; all 32,767 display in the formula bar.

So apparently the limitation is in the Autofit function.

 

[Charlie W]

Thanks. I'm just now seeing this months late. I assumed I'd be able to find
my post (and your response!) by searching the forums for
'AutoFitMergedCellRowHeight' but that doesn't seem to work. I stumbled onto
your reply today while searching for other info.

Apparently there's some sort of a limit (409 pixels?) to the autorowheight
functionality. I just hate to use 'hard' numbers in my code. I'd prefer to
use something like xlMaxRowHeight. But there doesn't seem to be any such
defined constant.

Thanks again.