-
[RESOLVED] Removing Line Feeds - Excel
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):
Code:
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:
Code:
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?
-
Re: Removing Line Feeds - Excel
Remove the line
Code:
Globals.ThisAddIn.Application.Selection.replace(What:=Chr(1), Replacement:=" ")
See the AscII table link in my signature...
-
Re: Removing Line Feeds - Excel
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...
-
Re: Removing Line Feeds - Excel
Show me the sample text in a cell...
-
1 Attachment(s)
Re: Removing Line Feeds - Excel
See the attached.
The text "P 0011349" changes to "P 001 49".
-
Re: Removing Line Feeds - Excel
Well I just tried the same text in my excel sheet and it didn't remove any 1 or 3 :confused:
Can I see a sample worksheet with data as in your last post...
-
1 Attachment(s)
Re: Removing Line Feeds - Excel
Attached.
If it makes a difference at all, I'm not doing this from VBA - I'm doing it from Visual Studio 2008.
-
Re: Removing Line Feeds - Excel
Quote:
I'm doing it from Visual StudioVisual Studio 2008.
Ah Ok... Let me see if it makes any difference...
BTW where is the attachment? :)
-
Re: Removing Line Feeds - Excel
Didn't realize you can't attach xls's - i zipped and attached to last post.
Thanks a ton man.
-
Re: Removing Line Feeds - Excel
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 :)
-
Re: Removing Line Feeds - Excel
New Code (still doesn't work):
Code:
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
-
Re: Removing Line Feeds - Excel
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?
-
Re: Removing Line Feeds - Excel
They both suck lol. The Convert.ToChar(10) line removes all the 10s, the Convert.ToChar(13) line removes all the 13s. :(:(:(
-
Re: Removing Line Feeds - Excel
What does this give you?
Code:
Msgbox Convert.ToChar(10)
Msgbox Convert.ToChar(13)
-
Re: Removing Line Feeds - Excel
A blank msgbox (i'm assuming there are line feeds).
-
Re: Removing Line Feeds - Excel
Quote:
Originally Posted by
fEtchboi88
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? :confused:
i am still trying to figure it out... :lol:
-
Re: Removing Line Feeds - Excel
Ya - I'm thoroughly stumped. NOT a fan :(
-
Re: Removing Line Feeds - Excel
Quote:
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?
-
Re: Removing Line Feeds - Excel
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!
-
Re: [RESOLVED] Removing Line Feeds - Excel
lolzzzz... Gald it is resolved.
I had gone through every cell but couldn't see any strange line feeds...
-
1 Attachment(s)
Re: [RESOLVED] Removing Line Feeds - Excel
Just kidding - I lied.
It's still not working...the attached contains the line feeds that wont go away.
-
Re: [RESOLVED] Removing Line Feeds - Excel
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.:sick:
-
Re: [RESOLVED] Removing Line Feeds - Excel
Are you sure you are not lying this time :ehh:
:lol:
If it works then definitely there is nothing wrong with the code. There is something else which is causing this erratic behavior....
-
Re: [RESOLVED] Removing Line Feeds - Excel
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!!