Find and Replace "," in Excel...Formula too long

[Jen123]

Ok, I've had a good rumage and can't find what I need and hope someone out there can help me.

I'm trying to create a macro in Excel to find and replace special characters such as a comma. I'm using the code below which works fine until it gets to a cell that is 1655 characters long. The formula just stops working. Can anyone suggest alternative code???? Ideally, I would like to change this to loop around a selection??

cells.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

 

[GlennUK]

I think we need more info .... what are the formulae that are over 1655 characters long? What's the purpose of changing the special characters?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Jen123]

Hi

Thanks for responding. Basically, if I do a find and replace on a cell that has 1655 characters in it, I get and error message 'Formula too long'.

I've actually managed to find some code which has sorted the problem.

Set Sh = ActiveSheet
Set rRng = Sh.Range("H1:I5000")

For Each r In rRng.Cells
r.Value = Replace(r.Value, Chr(13), "")
r.Value = Replace(r.Value, Chr(10), "")
r.Value = Replace(r.Value, vbKeyReturn, "")
r.Value = Replace(r.Value, ",", "")
r.Value = Replace(r.Value, "'", "")
r.Value = Replace(r.Value, "*", "")
r.Value = Replace(r.Value, ";", "")
r.Value = Replace(r.Value, ":", "")
r.Value = Replace(r.Value, "-", "")
r.Value = Replace(r.Value, "#", "")
Next

 

[SkipVought]

Be careful.

Removing a comma in a formula will reek havoc!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[GlennUK]

Hey Skip, shouldn't that be wreak havoc? Or is "reek" havoc the correct term for what your faulty macro did, and you have to spend a stinking hour or two undoing the damage?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

I knew that reek didn't look right.

O.L!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]