Excel fields too large to work with, any way around this?

**bxc2739** · 03-17-2006, 10:08 AM

Hi,

I believe once the Excel cells/fields get too big (individual cell content size) some of the format
and find/search/delete functions, macros, and utilities seize to work become it is somehow truncated and
the entire cell doesn't even get processed. How do I go around this inherent limitation?

I'm currently using ASAP Utilities for Excel. What I use most
often is the "Advanced Character Removal" Tool under the Text Menu of
ASAP. It works great, However this Advanced Character Removal tool completely FAILS once the individual field/cell sizes become too large.Some of the cells in my Excel sheet have a LOT of Data (mostly memos,words,email transcript(s),etc) within each of the indivudal cells.

I have a bunch of 010 and 013 empty box characters and use ASAP to help me get rid of them, however ASAP fails to erase anything from cells that contain too much data. (Note: I have used VB MACROS to accomplish the same thing, and again here once the cells get too large the same thing happens, which makes me thing this is some kind of limitation/bug in MS Excel itself!)

IS there anyway around this????

Here's a link to my previos post for clarification:
http://www.excelforum.com/showthread.php?t=523214

Lots of ideas from people have helped a lot, but now I'm stuck with
having too large fields, and I can't compromise the size, so what to do?
Both the macros and the ASAP utilities fail to response to the individual contents inside the cells once
each cell contains too much data!? Why should this happen? Is there a way to remove the Excel limit??Thanks,
Bo

Dave O · 03-17-2006, 11:15 AM

I think a bit of VBA code should do it for you: without seeing the
macros you ran earlier it's tough to say. This worked for me- give it
a try on some backup data, and please let me know how it worked out.

Sub No_010_013()
Dim rCell
Dim NewValue As Variant
Dim K As Long 'counter

For Each rCell In ActiveSheet.UsedRange
'If rCell.Value = "" Then GoTo Bailout:
For K = 1 To Len(rCell.Value)
If Asc(Mid(rCell.Value, K, 1)) <> 10 And Asc(Mid(rCell.Value, K,
1)) <> 13 Then
NewValue = NewValue & Mid(rCell.Value, K, 1)
End If
Next K
rCell.Value = NewValue
NewValue = ""
Bailout:
Next rCell
End Sub

**bxc2739** · 03-17-2006, 11:44 AM

Thanks for the response Dave,

FYI- the macros I used earlier yesterday come from this page: http://www.mvps.org/dmcritchie/excel/join.htm#trimall

It works, but only on short cell lengths, my long cells do not get affected so its no use for large fields.

I tested your macro (above post)
but unfortunately when I run/complied your macro it gave a compile error.

Syntax error for: If Asc(Mid(rCell.Value, K, 1)) <> 10 And Asc(Mid(rCell.Value, K,
1)) <> 13 Then

Bascially I just want to get rid of all the extra unneeded 010, and 013 (dec) characters in all the fields/cells of my VERY LONG excel sheet.
ASAP and other macros work, but only for cells of moderate length, when
each individual cell contains too much data (as in my case, each cell contains entire email correspondences) BOTH the macro and ASAP utilities utterly and completely fail to do anything at all!!! I was hoping your macro would
address the issue, but it has a sytnax error, could you take another look at it?

Thanks
Bo

Dave O · 03-17-2006, 12:00 PM

The newsgroup wrapped that line onto 3 lines: in your compiler that
constitutes a single line of code. Here is the code again (I've
removed some of the debugging lines I used) with a zzz at the beginning
of each line. Paste this code into your compiler, please, and arrange
it so each line starts with zzz. Any line that does NOT have a zzz on
it when you receive it belongs at the end of the previous line. When
you're done remove all the zzz entries- there are 14 of them- and it
will compile properly.

zzzSub No_010_013()
zzzDim rCell
zzzDim NewValue As Variant
zzzDim K As Long 'counter

zzzFor Each rCell In ActiveSheet.UsedRange
zzz For K = 1 To Len(rCell.Value)
zzz If Asc(Mid(rCell.Value, K, 1)) <> 10 And Asc(Mid(rCell.Value, K,
1)) <> 13 Then
zzz NewValue = NewValue & Mid(rCell.Value, K, 1)
zzz End If
zzz Next K
zzz rCell.Value = NewValue
zzz NewValue = ""
zzzNext rCell
zzzEnd Sub

Dave O · 03-17-2006, 12:10 PM

The newsgroup wrapped that line onto 3 lines: in your compiler that
constitutes a single line of code. Here is the code again (I've
removed some of the debugging lines I used) with a zzz at the beginning
of each line. Paste this code into your compiler, please, and arrange
it so each line starts with zzz. Any line that does NOT have a zzz on
it when you receive it belongs at the end of the previous line. When
you're done remove all the zzz entries- there are 14 of them- and it
will compile properly.

zzzSub No_010_013()
zzzDim rCell
zzzDim NewValue As Variant
zzzDim K As Long 'counter

zzzFor Each rCell In ActiveSheet.UsedRange
zzz For K = 1 To Len(rCell.Value)
zzz If Asc(Mid(rCell.Value, K, 1)) <> 10 And Asc(Mid(rCell.Value, K,
1)) <> 13 Then
zzz NewValue = NewValue & Mid(rCell.Value, K, 1)
zzz End If
zzz Next K
zzz rCell.Value = NewValue
zzz NewValue = ""
zzzNext rCell
zzzEnd Sub

**bxc2739** · 03-17-2006, 12:10 PM

Dave,

thanks! Your code worked great this time! It did get rid of a lot of the squares!
However after a while of processing it gave me an OUT OF MEMORY ERROR.
I have uploaded a screenshot of it here->> http://www.freewebs.com/bxc2739/

The problem is my excel file is very large, (both in terms of individual cell sizes, and also in terms of the number of raw cells itself) When I go down to around
7000 or so, the boxes are all still there.

If this is a memory problem, how can I specify that the macro does different sets at a time?? (Otherwise I can run it over and over again, but the onces not modified will still remained not modified and that is not very good)

edit/note: what I meant was, your macro seems to get rid of the squares at 1000 cells at a time, after which excel gives an OUT of MEMORY error. I except this is a hard limit in Excel and there is not much that can be done about it. But could you script the macro so that I could specifiy which range to do? (ie 1-1000, then 1001-2000, etc..) That ways I can overcome the memory/size limitations and still get the entire (20,000) file processed!
(other than this you macro works prefect!)

Thanks,
Bo

**bxc2739** · 03-17-2006, 12:38 PM

The reason I need it to be a range is because I can't just copy 1000 cells
into a blank sheet and process each and paste back into it again, EXCEL has
a nasty habit of truncating very long fields/cells. So when I copy it the cell contents get cut off! So I have to process it in certain ranges within the
sheet itself.

So, I tried to change the code, instead of For K = 7000 To Len(rCell.Value)
I used For K = 7000 To Len(rCell.Value) (wanting it to start from 7000 instead of 1) however after I ran the problem my whole excel file went blank and
data was all erased (I have backup) That was an unexpected behavior, what
did I do wrong?

Thanks

Dave O · 03-17-2006, 12:40 PM

The out of memory error is confounding me.

I'll write code to allow you specify a range: from your screenshot it
looks like everything is in Column A. In the meantime, the code I sent
earlier has this line:
rCell.Value = NewValue

As a test, please replace that line with this one
rCell.Value = "'" & NewValue
.... and try the code again.

Dave O · 03-17-2006, 12:50 PM

What I'm trying to do is get the code to run against whatever it may
encounter- the problem is memory allocation of the variables within the
code, not the number of cells. I suspect the code fails as it reaches
a particularly long string- so whether we tell it to run a thousand
cells at a time or just one, the code will fail at that point
regardless.

So proceeding with that theory, please take this line
Dim NewValue As Variant

....and replace it with this one
Dim NewValue As String

.... and let me know your results.

Also, would you prefer the code to replace the line feeds and carriage
returns with a space, instead of nothing?

**bxc2739** · 03-17-2006, 12:50 PM

Dave thanks!!!!, it worked! FInally works perfect !

All I had to do was change the code, like you recommended that one line and now it
processess EVERYTHING and no memory error!

-->> rCell.Value = "'" & NewValue <<--

Wow, that was like magic. I have no idea how you did that?!?!
Thanks again for all the prompt and helpful help! This has saved hours/days of time!

Dave O · 03-17-2006, 01:00 PM

The reason this change did not work for you
For K = 7000 To Len(rCell.Value)

is because this line treats the interior contents of each cell as it is
encountered, not the overall range of cells. When K is set to 1 the
program considers each cell starting at character number 1. When you
set it to 7000 it starts looking at character number 7000, so any cell
with less than 7000 characters comes back as blank.

Good on ya for working on backed up data!

**bxc2739** · 03-17-2006, 01:02 PM

Originally Posted by Dave O

Also, would you prefer the code to replace the line feeds and carriage
returns with a space, instead of nothing?

Is it at nothing right now? (I mean by default does the code return 'nothing',
) I think a space is preferably to nothing, so that the format will be exactly the same. But thats okay, big problem is solved, thanks Dave!

[no reply necessary]

Dave O · 03-17-2006, 01:10 PM

Cool! Glad it worked for you. Make sure your boss thinks you're a
genius, and take the rest of the day off.

Our posts are getting crossed, because of the delay in transferring
data from your website access point (ExcelTip) to mine. If you'd like
to enter a space instead of nothing, after this line
NewValue = NewValue & Mid(rCell.Value, K, 1)
.... add these two new lines before the End If
Else
NewValue = NewValue & " "

.... and you're good to go.