Macro for deleting empty rows within a cell

[McKommander]

I am looking for help in writing a macro to help me in deleting an empty row with in a cell for all the cells in a column. I only need one line in a cell.

Is there a way to write a Macro to delete the empty rows within each cell so that only one row exists, all the way down the column? My data has one only 2 lines with the first line empty. I can't break the text apart using the text to column function because excel seems to ignore any data in the second line.

Thanks

 

[McKommander]

Strangely enough when I run it twice it works for the rest of the data. It does delete a character from the first two rows but after that it seems to work fine. Don't know if I am doing something wrong?

 

[oldbrewer]

spell out what you want removing eg rows 6 and 7, 11 and 12 etc

 

[McKommander]

I don't think it copied correctly. It should look more like the following where every two rows represents one cell. Hopefully that makes sense. I need the first row of each cell deleted(or removed). Does that make sense?

[fTSH2,0]

[U007,0] [U009,0] [U019,0] [U008,0] [U049,0] [U118,0] [U094,0] [U074,0] [U124,0] [U083,0] [U502,0] [U501,0]

[U007,0] [U009,0] [U019,0] [U008,0] [U049,0] [U118,0] [U094,0] [U074,0] [U124,0] [U083,0] [U502,0] [U501,0]

[U099,0] [U076,0] [U037,0] [U007,0] [U009,0] [U019,0] [U008,0] [U131,0] [U031,0] [U041,0] [U044,0] [U040,0] [U118,0] [U065,0] [U082,0] [U124,0] [U083,0] [U502,0]

[FRT4,0]

[CK-MB,0]

[U099,0] [U076,0] [U037,0] [U007,0] [U009,0] [U019,0] [U008,0] [U131,0] [U031,0] [U041,0] [U044,0] [U118,0] [U065,0] [U124,0] [U083,0] [U502,0]

<tbody>
</tbody>

 

[McKommander]

Hi oldbrewer,
I think I figured out what the problem is. There is an unseen space in my data causing the problem. when I run it twice those lines that still had the space are removed it does remove an extra character from the lines that don't have the space, but luckily it's not something I need anyway.

Thank you for your help. I may need to go back to the original data and see how to parse the data so that I don't get the extra space.

 

[SpillerBD]

.... I can't break the text apart using the text to column function because excel seems to ignore any data in the second line.

Thanks

To use the CR for a delimiter in Text to Columns wizard, in the Other delimiter use the keystroke ALT+ 0010

 

[Rick Rothstein]

To use the CR for a delimiter in Text to Columns wizard, in the Other delimiter use the keystroke ALT+ 0010

If you do the ALT+0010 keystroke, the digits must be typed from the Numeric Keyboard, not the number keys above the letter keys. An alternate to that keystroke is to press CTRL+J instead.

 

[SpillerBD]

If you do the ALT+0010 keystroke, the digits must be typed from the Numeric Keyboard, not the number keys above the letter keys. An alternate to that keystroke is to press CTRL+J instead.

ray: