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

[bxc2739]

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 too
completely FAILS once the individual field/cell sizes become to
large.
Some of the cells in my Excel have a LOT of Data (mostl
memos,words,email transcript(s),etc) within each of the indivuda
cells.

I have a bunch of 010 and 013 empty box characters and use ASAP to hel
me get rid of them, however ASAP fails to erase anything from cells tha
contain too much data. (Note: I have used MACROS to accomplish the sam
thing, and again here once the cells get too large the same thin
happens, which makes me thing this is some kind of limitation/bug in M
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 t
do?

Thanks,
B

 

[Dave O]

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]

Thanks for the response Dave,

but unfortunately when I run/complied your macro it gave a compil
error.

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

Bascially I just want to get rid of all the extra unneeded 010, and 01
(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 cel
contains entire email correspondences) BOTH the macro and ASA
utilities utterly and completely fail to do anything at all!!! I wa
hoping your macro would
address the issue, but it has a sytnax error, could you take a look a
it?


Thanks
B

 

[Dave O]

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]

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]

Dave

thanks! Your code worked great this time! It did get rid of a lot o
the squares
However after a while of processing it gave me an *OUT OF MEMOR
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 o
individual cell sizes, and also in terms of the number of raw cell
itself) When I go down to aroun
7000 or so, the boxes are all still there.

If this is a memory problem, how can I specify that the macro doe
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 i
not very good

edit/note: what I meant was, your macro seems to get rid of the square
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 b
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 th
memory/size limitations and still get the entire (20,000) fil
processed
(other than this you macro works prefect!

Thanks
B

 

[Dave O]

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.

 

[bxc2739]

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

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


Thank

 

[Dave O]

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]

_DAVE_THANKS!!!!,_[/B] IT WORKED! _FINALLY_WORKS_PERFECT_!
All I had to do was change the code, like you recommended that one lin
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 hour
of time

 

[Dave O]

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]

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 retur
'nothing',
) I think a space is preferably to nothing, so that the format will b
exactly the same. But thats okay, big problem is solved, thanks Dave!

[no reply necessary

 

[Dave O]

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.