Excel not reading blank lines

[Maximus89]

I have data in Excel which I imported from another program. The result is displaying the text in cells with 4 blank lines before the text, so in a single cell it would have something like this:

<Blank Line>
<Blank Line>
<Blank Line>
<Blank Line>
TEXT


My problem is my VBA code to remove these lines doesn't appear to be picking them up correctly:

Columns("A").Replace What:=Chr(10)&Chr(10)&Chr(10)&Chr(10), Replacement:="", _
'LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False

It works when I look for the a single Chr(10) but not when there are 4 together, even though there are 4 blank lines together within the cell.

Can anyone help?

Thanks

[TMS]

I think it would just be Alt+010, not 0010.

Post a sample workbook. Let's have a look.

Regards, TMS

[Pete_UK]

Try it with ALT-013 or CTRL-M. If that doesn't do anything, post an example workbook (only needs a few rows).

Hope this helps.

Pete

[Maximus89]

Sorry, post edited with more information. See original post

[TMS]

Like we've both said, please post a sample workbook. Probably only need to see the one cell.

[Pete_UK]

I've seen your edited reply (it is not a good idea to change an earlier post).

It might be that you have a space (or a few of them) on each blank line, so replacing a single Chr(10) will get rid of the blank rows (leaving just the spaces), but Chr(10)&Chr(10)&Chr(10)&Chr(10) won't work.

Hope this helps.

Pete

[Maximus89]

Thanks,

Sample.xlsx

I have attached a sample and apologies for the earlier editing. If the 4 chr(10) doesn't work, is there another way I can ensure all 4 lines are removed without removing any remaining blank lines which appear later in the cell?

The cells with this issue are:

A2-4
B2-4
F2

[Pete_UK]

It would appear that you have Chr(13)&Chr(10) appearing 4 times at the beginning of cells A2:B4 in this sample file, as well as in F2. You also have those two characters appearing in pairs further along the text in those cells. I don't know why you have the Chr(13) in there, as it is not usually used by Excel - have you imported this data from a website (or from a Mac) ?

Hope this helps.

Pete

[Maximus89]

Excellent! That has helped a lot. The data was imported from HP Quality Center, not too sure why they get converted into Chr(13).

I have changed the code to:

Columns("A:O").Replace What:=Chr(10)&Chr(13), Replacement:="", _
LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False

It now removes all those empty lines except for one which remains on top, any idea why this is or how I can get rid of it?

[Pete_UK]

Put them the other way round, i.e. Chr(13)&Chr(10)

Hope this helps.

Pete

[TMS]

If you ever need to do it again, this subroutine might help:

Please Login or Register  to view this content.

Output for cell A2:

Please Login or Register  to view this content.

Regards, TMS

[Pete_UK]

Thanks for that, Trevor - I did a similar thing but using CODE and MID to find out the actual structure of the entires.

Pete

[TMS]

Hi Pete. Yes, I've done that in the past, but it relies on my counting skills to work out which character(s) I want to look at, unless I go to the trouble of dragging a formula down/across.

I looked at the structure and length of the cells and decided a subroutine might be useful. Could make it a function too ...

Please Login or Register  to view this content.

Regards, TMS

[Maximus89]

That worked. The output is now exactly how I want it.

I will also save that piece of code for future reference.

Thanks!

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.