Ok. The "If .Shape.Width > 0 Then" seems to have originally been something like "If .Shape.Width > 300 Then" so that it would not apply it to short comments (or ones with one or more short lines). However I don't see that would have made any significant difference. The sheet you linked still has that >0 code, but it should always be true so it would always execute the size change code.

Which is the short "truncated" comment? The "Hawksmoor" one? If so the problem probably stems from the fact that the last line is only a single word. Basically this is kind of the opposite effect of the main problem. I think that Excel's numbers returned for Width are not the most accurate. Also it has a short description and only one word on the last line.

I think you need a +1 line or similar to round up to the next whole number of lines. Other wise if you have 2.5 lines of text your box will only be 2.5 lines high. Larger amounts of text tend to average that difference out/away

It looks like a standard line height is 13.5. So the original numbers look like this:
Width 704.25
Height 13.5
Area = 9507

Scale that to 305 wide and you get
Height = 9507 / 305 = 31.1
which is only enough to show two and a bit lines.

Really that 31.1 should be more like 40.5

Sooo... if we can assume a line is 13.5 high I would do something like this (thinking aloud).

Height = 9507 / 305
Lines = Height / 13.5
Lines += 1
Height = Lines * 13.5

Where Lines is an integer to ensure it's rounded down. Doing that doesn't appear to work since a line height isn't 13.5 it seems (that's what's reported for a single line). So it's 13.5 for the first line and then x for each line after that probably. This is all getting quite clunky and yucky with the hardcoded 13.5... Change the font and it all breaks.

Or just add 1 line height (once you figure it out). You'll have between 0 and 1 extra lines if you do on every comment).

For the main issue, are you sure you want to lose your paragraphs?

Biggest problem here is really Excel's text handling. It just doesn't quite work. A good example is wrapping text in a cell (so it changes the height automatically). If the text is close the length of the cell (but doesn't actually wrap), it still adds the extra line sometimes. Similarly once cells get simply too big it fails to calculate the correct height and the text runs off both the top and bottom of the cell.