Hi,

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 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 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:

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?

Thanks,
Bo


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523549

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

Thanks for the response Dave,

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 a look at
it?


Thanks
Bo


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523549

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

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,

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_s pecifiy_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
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523549

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.

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


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523549

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?

_DAVE_THANKS!!!!,_[/b] IT WORKED! [b]_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!


Wow, that was like magic.
Thanks again for all the prompt and helpful help! This has saved hours
of time!


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523549

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!

Dave O Wrote:
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]


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523549

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.