{Problem Solved} VBA - Excel - Weirdness

Ok, so i have written a function in VBA to loop through an entire sheet and remove all Chr(10) & 13, separately because yes, sometimes it does happen ( I hate it too).

So, I need to remove them because this piece of software I work with cannot have either of them since it reads CSV's based on the Linux format (Separated by Chr(10) instead of 13 & 10, CrLf).

Now, the thing is, when I run this code it removes them all with no problem. But, it also seems to remove a good 40% of the text. Cuts it off at the end for some reason.

I think I may be using the wrong cell function to do it, but cannot yet be sure as my 1200 page book has no information regarding the difference.

Check out my code below and let me know what you think.

VB Code:

If InStr(1, Range("$" & CellLoc & "$" & i).Text, Chr(10)) Then
            SpcReport = SpcReport & CellLoc & i & " contained at least one Line Feed" & Chr(10)
            Range("$" & CellLoc & "$" & i).FormulaR1C1 = Replace(Range("$" & CellLoc & "$" & i).Text, Chr(10), "")
          End If
          
          If InStr(1, Range("$" & CellLoc & "$" & i).Text, Chr(13)) Then
            SpcReport = SpcReport & CellLoc & i & " contained at least one Carriage Return" & Chr(10)
            Range("$" & CellLoc & "$" & i).FormulaR1C1 = Replace(Range("$" & CellLoc & "$" & i).Text, Chr(13), "")
          End If

Now, Range("$" & CellLoc & "$" & i) gets the current cell being checked. This works super-duper....

But, any attempts to fix the fact that it removes most of the hud at the end of the cell's text have failed.

I wrote an app in VB6 that will open the file and remove it that way but the one I need to distribute at work is the VBA module (Stupid management rules).

So, anyone who can offer advice on why it's slaughtering the text will be very appreciated.

Thanks.

Alright, it seems that FormulaR1C1 only holds 1,024 chatacters.... Which makes sense but I could find no documentation on it.

Now what I need is to find which method will hold as many as I need.

I will update this when I resolve the problem.

Do you have to have the reporting? If not, you could use:

Cells.Replace What:=Chr(10), Replacement:=Null, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:=Chr(13), Replacement:=Null, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Hope that helps!

The reporting is essential, but that's not causing an issue as I can always drop it in if the search critera is met.

I did try this method, however, it didn't remove the line breaks after all. There were no errors but the text stayed as is. Which may in fact be another Excel issue.

I think i may end up needing to write a function that will chunk the text into the cell 1,024 characters at a time, but I am exploring every other possible route to take.

Thanks for your reply, it is very appreciated.

Ok, figured it out.

As it turns out the limit is coming from the Text property. So:

VB Code:

Strdata = Range("$A$1").Text

Is limited to 1,024 characters. Try to find some documentation on this, I dare you. I looked all over MSDN, my Excel power programming in VBA book (1200 pages). And nowhere does it state about a length limit.

Thanks to a co-worker, this is what ended up being the solution:

VB Code:

ActiveCell.Value = Join(Split(ActiveCell.Value, vbLf), " ")

So from now on, make sure you use Value, not text when pulling hud out of a cell.

Thanks everyone and enjoy.