Visible rows in cell - vba

[Bags]

Hi there - I have a calendar that is generated in Excel that I am having an issue with.

There are instances where the text in the cell is bigger than the cell size and doesn't appear - I'm not talking about the cell limit of characters showing, simply the size of the cell has not been expanded/autofit to show the contents of the cell.

I don't want to autofit the cell since the formatting of the calendar is important to me and for reports so I would like the cell size to remain.

I am looking for a way to flag when there is text in the cell that is not visible. My initial thought is to count the number of lines of text that are visible in the cell vs. the lines of text in the cell - and if the text in cell lines is bigger, highlight the cell or inserta cell comment or something.

Is there a way to count the number of rows of text as it appears in a cell - or any other way you can think of to handle this?

Thanks for any help.

 

[pbornemeier]

You could count the number of characters in the cell and based on the current font (average character width) and the width of the cell figure out about how many rows were there. You may have to take carriage returns & line feeds into account due to the "loss" of character space after them.

You could also do the above then automatically change the font size to allow all of the characters to be visible.

 

[Bags]

Thanks for your response. That's what I was originally thinking, however there may only be one word in the row, in which case all of the empty space in that row is not included when counting the chars. I also looked at counting the carriage returns, and it was close, but doesn't consider the wrapped text. Not sure what else to try?

 

[pbornemeier]

Paste the data that would go into each cell into cell A1 on another worksheet that had the same width and font settings, but that was set to word wrap. execute a row autofit on that row, then divide by the height of an unmodified row to get the number of rows used.

    Rows(1).AutoFit
    RowHeight = Rows(1).Height / Rows(2).Height