Fix AutoFit Row Height gap with vba

[Bond00]

So when selecting for a cell to auto adjust row height it sometimes leaves a gap in the cell at top or bottom depending on what format is selected.
like this

is there a way to detect when that happens and account for it in vba?
Like looping through and finding which rows have that gap and manually remove that added line of space?
Rows(i).RowHeight = current row height of i - 14.4 '(seems 14.4 is the added space that can be removed always)

 

[Micron]

That may contain non-printable characters so it would be important to know before trying any vba. Try going to the immediate window and typing
?asc(Right(Sheets("MySheetNameHere").Range("A1"),1)) and hit enter. Use your own sheet name and range of course. If it does contain such characters, I'm guessing that the result will be 13, or more likely, 10. If you see nothing returned by that, then it's likely just empty space as you say.

EDIT - another test could be Len(). I have "dog" in a cell to test this, and the result is 4. Obviously it is picking up my linefeed character:
?len(Sheets("7").Range("H12"))

 

[Joe4]

I often see the same sort of thing in Excel - your text entry visually fits on one line, but it seems to push the cell to a second line.
It is always when the text is close to the end of cell. Usually, I just extend the column width a fraction larger, and re-enter the entry and then it collapses back down.

I think you may be able to do something with font or font size to get it to behave more like you would expect.
I recall seeing something once, but I don't remember what the recommend font to use was.

 

[Bond00]

Yeah there is no space at the end of the last word. Nothing hidden, its just the way the cell acts when its on Wrap text and auto adjust row height for that cell.
See this has auto adjust row height but no wrapped text on yet.

This is with wrapped text on now.

Happens just randomly when something just barely fits i guess.
The thing is i cant really mess with manually adjusting or changing font/size etc. its a form output that needs to be auto.

So it would be nice to somehow detect how many characters are in there vs testing the height of the row or something.. There has to be some way to see if there is an added pointless white space in a cell..

The issue is it does it with 36 char's all in a row too or 72 chars if its 2 lines but then with i's or some thinner character its a different count so theres no perfect way to know when it will do it..

Here you can see for yourself. past this into excel and make the column width 28.22 to test this.
test test test test test test test test test test test test
test test test test test test test test test test test test test test test test test test test test test test test test test test
test test test test test test test test test test test test test test test test
test test test test test test test test test test
test test test test test test test test
test test test test test test test test test test test test test test test test test test
test test test test test test test test test test test test test test test test test test test test

 

[Micron]

How many rows might be an issue and can a user interact with the sheet to fix it up? I'm wondering if a double click event on the cell could cut the contents, widen the column by a small amount, then paste the content back in. IMO, there's no chance of automating it unless you use a fixed width font.

 

[Joe4]

Happens just randomly when something just barely fits i guess.

Yes, that is when I see it happen too.

I think I found where it says which fonts may work best - try Courier New.
See: Microsoft-Supplied Monospaced TrueType Fonts - Microsoft Support

 

[Micron]

That article may be out of date. I read that 2 others were supposed to be added some time after 2021. I have one of them (Consolas) but not the other (Cascadia). There is also the possibility to download fonts that were not developed by M$ but you wouldn't want to use those if the file is shared.
This list might be out of date as well, but I think it's more likely to get updated than M$ documentation.

List of monospaced typefaces - Wikipedia

en.wikipedia.org

Fixed width and monospaced mean the same thing wrt fonts.

 

[Bond00]

How many rows might be an issue and can a user interact with the sheet to fix it up? I'm wondering if a double click event on the cell could cut the contents, widen the column by a small amount, then paste the content back in. IMO, there's no chance of automating it unless you use a fixed width font.

a double click on it has no effect on it, just stays that same size thats oversized by 14.4.

That article may be out of date. I read that 2 others were supposed to be added some time after 2021. I have one of them (Consolas) but not the other (Cascadia). There is also the possibility to download fonts that were not developed by M$ but you wouldn't want to use those if the file is shared.
This list might be out of date as well, but I think it's more likely to get updated than M$ documentation.

List of monospaced typefaces - Wikipedia

en.wikipedia.org

Fixed width and monospaced mean the same thing wrt fonts.

Oh ok so the mono spaced fonts dont have that issue like this? or just easier to calculate how many spaces it will take and then compare to the height of the row.

Also yes the page isnt locked, so it can be manually adjusted if needed, just was trying to avoid that. but seems there may not be a perfect method for that.

Is it possible to somehow detect how many times the text wraps? or how many returns are in the cell or anything like that?

 

[Micron]

a double click on it has no effect on it, just stays that same size thats oversized by 14.4.

You misunderstood. I'm suggesting a double click (or some other trigger) would run code, not simply take care of the issue.
The problem with proportional fonts for this is that my made up words - "wwww" and "iiii" have the same character count yet they are not the same physical length. Therefore you cannot rely on character count in a cell to provide any guidance on the physical space it occupies if you are going to use proportional fonts. If the font was fixed width (aka monospaced) then any 4 characters (to use 4 as an example) will be the same physical length as any 4 characters. With these fonts, a "W" occupies the same space as an "i"". Since you are dealing with physical space (i.e. column width) in a sheet, only fixed width fonts of any count will occupy the same space.

As I see it, knowing what is the physical width of any particular fixed width font (and here, size will matter) would be needed. Add to that, the physical width of the column would need to be known so that you can correlate character count (thus the width of the cell value) to the width of the cell, and you can bet your bippy that one is in pixels or points, and the other is in inches or centimeters/millimeters. This is not something I'm prepared to delve into. You might find that this has already been solved somewhere, but maybe at least at this point you know what to look for.

 

[Bond00]

yeah i guess i can mess around with it in the ghetto way of counting, I was just hopeful there was some better way i wasn't aware of.