[RESOLVED] Removing Line Feeds - Excel

[fEtchboi88]

My Excel addin has a feature to remove all line feeds and replace them with a space.

Originally, I had just this (and it worked fine):

        Globals.ThisAddIn.Application.ScreenUpdating = False
        Globals.ThisAddIn.Application.DisplayAlerts = False
        Globals.ThisAddIn.Application.Selection.replace(What:=vbNewLine, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbCr, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbLf, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbCrLf, Replacement:=" ")
        Globals.ThisAddIn.Application.ScreenUpdating = True
        Globals.ThisAddIn.Application.DisplayAlerts = True

However - I started coming across line feeds that weren't being removed by that code. After playing with it a bit, I ended up getting the ASCII codes for the line feeds I couldn't remove.

I then altered my code to this:

        Globals.ThisAddIn.Application.ScreenUpdating = False
        Globals.ThisAddIn.Application.DisplayAlerts = False
        Globals.ThisAddIn.Application.Selection.replace(What:=vbNewLine, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbCr, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbLf, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbCrLf, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=Chr(10), Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=Chr(1), Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=Chr(3), Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=Chr(13), Replacement:=" ")
        Globals.ThisAddIn.Application.ScreenUpdating = True
        Globals.ThisAddIn.Application.DisplayAlerts = True

This works fabulously, however it replaces all 10's, 1's, 3's, and 13's with a space as well...which is bad.

Does anyone know how I can prevent the code from replacing the numbers?

[Siddharth Rout]

Remove the line

Globals.ThisAddIn.Application.Selection.replace(What:=Chr(1), Replacement:=" ")

See the AscII table link in my signature...

[fEtchboi88]

I did it, and it no longer removes 1's (or 3's as i took that line out as well), but it still removes 10's and 13's...

[Siddharth Rout]

Show me the sample text in a cell...

[fEtchboi88]

See the attached.

The text "P 0011349" changes to "P 001 49".

[Siddharth Rout]

Well I just tried the same text in my excel sheet and it didn't remove any 1 or 3

Can I see a sample worksheet with data as in your last post...

[fEtchboi88]

Attached.

If it makes a difference at all, I'm not doing this from VBA - I'm doing it from Visual Studio 2008.

[Siddharth Rout]

I'm doing it from Visual StudioVisual Studio 2008.

Ah Ok... Let me see if it makes any difference...

BTW where is the attachment?

[fEtchboi88]

Didn't realize you can't attach xls's - i zipped and attached to last post.

Thanks a ton man.

[Siddharth Rout]

Ok I tried in VBA and I didn't see the ones and three's getting deleted...

If I am not wrong, in vb.net you have something like Convert.ToChar(Int32) in System.Convert class. Let me check and get back to you...

Edit:
Also check this for me. One by one remove the line having CHR() and see including which line is giving you the trouble

[fEtchboi88]

New Code (still doesn't work):

        Globals.ThisAddIn.Application.ScreenUpdating = False
        Globals.ThisAddIn.Application.DisplayAlerts = False
        Globals.ThisAddIn.Application.Selection.replace(What:=vbNewLine, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbCr, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbLf, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=vbCrLf, Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=Convert.ToChar(10), Replacement:=" ")
        Globals.ThisAddIn.Application.Selection.replace(What:=Convert.ToChar(13), Replacement:=" ")
        Globals.ThisAddIn.Application.ScreenUpdating = True
        Globals.ThisAddIn.Application.DisplayAlerts = True

[Siddharth Rout]

Check this for me. One by one remove the line having CHR() and see including which line is giving you the trouble... Is it the 10 or the 13?

[fEtchboi88]

They both suck lol. The Convert.ToChar(10) line removes all the 10s, the Convert.ToChar(13) line removes all the 13s.

[Siddharth Rout]

What does this give you?

Msgbox Convert.ToChar(10)
Msgbox Convert.ToChar(13)

[fEtchboi88]

A blank msgbox (i'm assuming there are line feeds).

[Siddharth Rout]

A blank msgbox (i'm assuming there are line feeds).

Exactly, I was expecting what you got... so how does it find 10 or 13 and replace?

i am still trying to figure it out...

[fEtchboi88]

Ya - I'm thoroughly stumped. NOT a fan

[Siddharth Rout]

However - I started coming across line feeds that weren't being removed by that code.

I refuse to give up . Does the Excel sheet that you posted have any examples of the above?

[fEtchboi88]

OOOOOK - I swear to god those line feeds wouldn't remove with my original code, but I just tried it again and it worked without the added CHR lines. So I guess crisis averted?

Thanks for your help!

[Siddharth Rout]

lolzzzz... Gald it is resolved.

I had gone through every cell but couldn't see any strange line feeds...

[fEtchboi88]

Just kidding - I lied.

It's still not working...the attached contains the line feeds that wont go away.

[fEtchboi88]

I'm really confused now.

I opened the sheet I just attached, and ran the utility, and it did not work.

I put the 10 & 13 lines back in, and it worked, but removed the 10s and 13s.

I took the 10 & 13 lines back out, and tried it again, and it worked (and didn't remove the 10s and 13s). But it's still not working on the original spreadsheet.

I'm BAFFLED.

[Siddharth Rout]

Are you sure you are not lying this time



If it works then definitely there is nothing wrong with the code. There is something else which is causing this erratic behavior....

[fEtchboi88]

Ya - IDK. Now it's working on the original spreadsheet, too, so it had to be formatting or something. I thought maybe because it was an .xlsx it wasn't working, but now it's working for that too.

Let's save us both a headache and not look into it anymore haha.

Thanks!!