Excel EntireRow.AutoFit 1

[Andrzejek]

Excel 2010, I populate cells with the data from the data base. Cells look like this with text entered with Alt-Enter which in VBA code is Chr(10)

[pre]
+----------------------+
|Some text here |
|some more text here |
|last line of text |
| |
| |
=----------------------+
[/pre]
I have just a few columns and in each cell in the Row there are no more than 3 lines of text, but the Cells display 'empty' lines of text (2 empty lines shown here).

I can select entire Row to auto adjust height, but I can still see the non-existing empty lines in the cell. But there is nothing below the 3 text lines.

How can I auto adjust height of the cells?


Have fun.

---- Andy

 

[SkipVought]

hi,

Use LEN() and LEFT() to return a column that contains the 3 lines per cell.

COPY that column and PasteSpecial -- VALUES over the source column.
[tt]
=Left(A2,Len(A2)-1)
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

I don’t really follow, Skip.
I have some text in Columns A, B, C, and D
Column B is the one with some extra ‘stuff’ in it.
To make the matter worse, I had to use a couple of different Fonts in the same cell to just align some data – it is a report for some users, and Excel is their choice.


Have fun.

---- Andy

 

[SkipVought]

Column B is the one with some extra ‘stuff’ in it.

Then change the reference to B
[tt]
=Left(B2,Len(B2)-2)
[/tt]

Sorry, it should have been -2, not -1

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

you could also use a procedure lis this

Sub removeLF()
    Dim r As Range, i As Integer
    
    With ActiveSheet
        For Each r In Intersect(.Columns(2), .UsedRange)
            For i = Len(r.Value) To 1 Step -1
                If Asc(Mid(r.Value, i, 1)) = 10 Then
                    r.Value = Left(r.Value, Len(r.Value) - 1)
                Else
                    Exit For
                End If
            Next
        Next
    End With
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Thank you Skip,
I did run your code, step thru it, but it never got to the line:
[tt]
r.Value = Left(r.Value, Len(r.Value) - 1)
[/tt]
So I guess it is something else than LineFeed character.

I am just going to give it 'as is' to the users and see if they start to complain about it. Who knows, they just may like to have some extra 'white space' in their report....

Have fun.

---- Andy

 

[SkipVought]

I guess it is something else than LineFeed character.

I thouhgt you knew what character it is. Why are you guessing? You're the programmer! You can determine the character(s) that are appended. Pretty simple to do via code and Debug.Print/Immediate Window.
Once you have found the code(s), you could do something like this...

Sub removeLF()
    Dim r As Range, i As Integer
    
    With ActiveSheet
        For Each r In Intersect(.Columns(2), .UsedRange)
            For i = Len(r.Value) To 1 Step -1
                Select Case Asc(Mid(r.Value, i, 1))
                    Case 10, 13
                         r.Value = Left(r.Value, Len(r.Value) - 1)
                    Case Else
                         Exit For
                End Select
            Next
        Next
    End With
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Really, I think that this would work perfectly well...

If Asc(Mid(r.Value, i, 1)) [highlight]< 32[/highlight] Then

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

I did try both: Select Case to check for Asc 10 and 13, and any Asc < 32, found none :-(
I know what I put into the cells, that's why I don't know why Excel refuses to auto-fit the rows. I even deleted all other columns and I left just one cell populated with the data.
Just to make sure I am looking at the right stuff, I included:[tt]
Debug.Print "*" & r.Value & "*"[/tt]
To SEE what’s before and after anything in the cell. No indication of anything strange. No empty places for any unprintable characters – that’s why none are detected by any code.

The only think that may trip Excel may be (my guess, again):
The text I put into the cells: first few lines of text are Arial font, then a few lines are Courier New font, then a few lines of Arial again.

Another test I did: copy the ‘trouble’ cell, I open another empty Excel file, pasted into the new Excel and it works fine: auto-fit rows works like a dream in the newly open Excel file. So I did the opposite: selected cell in the new Excel file, copied it, and pasted it back into Excel 1 file – I still have some space at the bottom of the cell :-(



Have fun.

---- Andy

 

[SkipVought]

Do you have space at the bottom of the cell OR is the AutoFit not working because some other column has multi-line data? ie do you have junk in other columns?

It seems suspicious that, "Another test I did: copy the ‘trouble’ cell, I open another empty Excel file, pasted into the new Excel and it works fine."

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

The cell shows up pretty much like in my OP, shows some empty space at the bottom, even tho there are no empty lines or any other characters there in the cell.

Auto-fit does work, it just doesn't eliminate this empty space. I can resize the rows by hand, and then auto-fit rows, but the space stays :-(

There are a few other cells in the Row with multi-line text, but even if I delete everything else and just leave the 'trouble' column, or even one 'trouble' cell, the white space is there.

I am suspicious, too, with copying/pasting and having/not having this issue.

I appreciate your help (a lot) but I will let it go for now. If users will cry about it, I will revisit it.
Or I will tell them: it is not a bug, it is a feature, and be happy with it

Have fun.

---- Andy

 

[SkipVought]

I'd be absolutely sure that ABSOLUTELY NOTHING is in "unused" cells to the right of this table, by explicitly selecting ALL this columns and right-click > delete. There can be DATA in cells that seem to have NOTHING in them. Kind of like Lawrence Krauss' A Universe from Nothing: Why There is Something Rather than Nothing

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Just for the piece of mind, and my sanity….
Selected column A, right-click delete
Selected column C, Shift-End to select all of them to the right, right-click delete.
One column left – the same behavior :-(
I will give it a rest.


Have fun.

---- Andy

 

[SkipVought]

must be a FEATURE! ;-)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

It's a bug in excel. Try to slightly increase the width of columns and next autofit height. There are reports that some fonts, some font sizes and the zoom below 100% cause more problems.

combo

 

[Andrzejek]

WOW! Combo, thanks.
I increased the width of the column and my 'feature' of extra white space is gone
Auto-fit columns shows some extra space (a lot of it), column is wider than it needs to be, but I can adjust it very easy.

Skip, thanks. You put a lot of work in helping me and I appreciate it.


Have fun.

---- Andy