Clean, Trim & Autofit will not work

[Stuart]

Importing certain files into Excel cause some rows where
principally text is in column B to contain empty lines.

I cannot identify these cells programmatically, so I
currently let the user run a basic import routine, then display:

MsgBox ("The EBQ file(s) are now imported and formatted into Excel." &
vbNewLine & _
"There will be occasions where cells in column B have an extra
empty line." _
& vbNewLine & "Currently, these empty lines must be marked by
you manually." _
& vbNewLine & vbNewLine & _
"Open the new files (every file in that new folder) and place #
in col G" _
& vbNewLine & "in every row that has such an empty line." &
vbNewLine & _
"Do this for every sheet in every workbook." & vbNewLine &
vbNewLine & _
"Save each workbook, then run the 'HASH' sub on those
workbooks." & _
vbNewLine & vbNewLine & _
"Once you have done this, you can run the 'PageResize'
routines.")

I'm now relying on them to accurately find every instance of the blank line
throughout every sheet and book they just imported.

The delete routine is simple, since it's always just one blank line. So find
'#',
establish the rowheight, and then reduce by 12.75 (the default).

Is there anything else I could try, please?

Regards.

 

[NickHK]

Stuart,
What do you get if you put this in C2 for example:
=Code(Right(B2,1))

i.e. check the code of the last character in column B?

NickHK

 

[Stuart]

Thanks for the reply.

In every case that I've tried that test, it has returned
the last character in the cell, even though the cell
shows an empty line after that character.

Regards.

 

[NickHK]

Stuart,
What happens if you set the formatting so that the text is aligned with the
bottom of the cell ? Does the extra line disappear ?

NickHK

 

[Stuart]

Already tried that......empty line moves to the top.

Tried copying cell contents to a new cell then autofit
..........same result, an extra line.

Regards.

 

[NickHK]

Stuart,
After playing around a bit, I see what you mean. The row height can be set
incorrectly, with an extra line, for a cell whose text should fit.
Can't find a logical reason for it yet, but it seems that Excel sometimes
gets confused over the real height required to show all the text.

NickHK

 

[Stuart]

Thanks for looking at the problem.
If you should find any kind of solution, then please
email me at (e-mail address removed).

Regards.