Removing the line feed in an Excel Cell

Topic

New Reply

Is there a vba script, or some code that i can use to remove the line feed or carriage return (Chr(10)) in a cell, or a range of cells? (Shows up as a small square). I have seen one that will remove the hidden apostrophe, but i’m not a vba programmer so I have no idea how to change it for my needs. Any help would be greatly appreciated.

Reply | Quote

Replies

Hi,
You can use something like this:
Sub ClearNulls()
Selection.Replace What:=Chr(10), Replacement:=””, lookat:=xlPart
End Sub
Just highlight a column or range and then run it. (You might want to try different characters – I do a lot of importing from Oracle which produces lots of little boxes but I need to use Chr(9) rather than Chr(10) to remove them. Try selecting a cell that just has the little box in and running
Sub CheckChar()
msgbox asc(selection)
end sub
to check what the character is)
Hope that helps.

Reply | Quote

When i run the CheckChar it comes up with a msg box that says “74”…

Reply | Quote

What font is the cell formatted in? 74 should be a ‘J’!

Reply | Quote

Well J was the first letter that was in the cell, maybe that’s why….i’ll try another…

Reply | Quote

My fault, it does come up with “13” when I do a cell that has JUST the square in it.

Reply | Quote

ok this is gonna sound weird, but what i ended up doing was changing the code everytime i ran it. It didnt’ remove all of them when i used chr(10) so I changed it to chr(13), that removed a few more, then changed it to chr(9), and that ended up removing all the rest. Seems like I have to do it multiple times…any ideas?

Reply | Quote

If that got all of them then you can change the code to read:
Sub ClearNulls()
With Selection
.Replace What:=Chr(9), Replacement:=””, lookat:=xlPart
.Replace What:=Chr(10), Replacement:=””, lookat:=xlPart
.Replace What:=Chr(13), Replacement:=””, lookat:=xlPart
end with
End Sub
and it should do all of them in one go!

Reply | Quote

one more question, how can i save this as a macro that can be used on any sheet, because we are getting more and more data and i would hate to have to make a new macro everytime, or is that how it’s gonna have to be done?

Reply | Quote

If you have a personal macro workbook, copy it into a module in there. Otherwise, choose Tools-Record Macro and choose to store it in a personal macro book (this will automatically create a workbook in your XLStart directory), stop recording without doing anything, then you can paste the macro into that workbook in the VBEditor.)
If that’s unclear let me know and I’ll try and explain it better!

Reply | Quote

clear as a bell, thanks again, you just saved me a ton of work….have a good one!

Reply | Quote