Search code examples
excelvbaformattingline-breaks

Excel VBA text import not formatting line-breaks correctly until double-clicking on cell

I have a VBA routine that imports a CSV and then fits it to a specific table for the correct dates. The import works correctly, but the line-breaks do not display correctly, even though they are displayed correctly in the formula bar.

If you double-click the cell then the formatting displays correctly, but there are many lines within the table and I do not want to have to do it for each cell individually.

Can anyone explain why this is happening, is there a special way to enter the text into the cell with VBA? Or is there a command that I need to add at the end of the import?

I am using wordwrap and also auto row-height.

This is how I am inputting the text into the cells.

            ws.Range("E" & LineRow).value = ReplaceCharacters(LineItems(1))
            ws.Range("F" & LineRow).value = ReplaceCharacters(LineItems(2))
            ws.Range("G" & LineRow).value = ReplaceCharacters(LineItems(3))
            ws.Range("H" & LineRow).value = ReplaceCharacters(LineItems(4))
            ws.Range("I" & LineRow).value = ReplaceCharacters(LineItems(5))
            ws.Range("J" & LineRow).value = ReplaceCharacters(LineItems(6))
            ws.Range("K" & LineRow).value = ReplaceCharacters(LineItems(7))
            ws.Range("L" & LineRow).value = ReplaceCharacters(LineItems(8))

This is how the text appears in the table upon input.

enter image description here

And this is how it appears in the formula bar.

enter image description here

Once I double-click the cell, this is how the cell appears (which is correct).

enter image description here

Solution 
  • ....
ws.Range("E" & LineRow).value = Replace(ReplaceCharacters(LineItems(1)), vbCr, vbCrLf, 1, , vbBinaryCompare)
....

    or modify internally the ReplaceCharacters function, to do the replacement.