want Replace ALT+ENTER to SPACE

[Avadivelan TCS]

Hi All,
I wrote one macro for replace for ALT+ENTER to SPACE. It's work well.
But if the ONE particular cell (like A1) contains more than 200
entires(1234, 4567, 7895,.. etc) means , at that time it will not work, it
says "Formula is too long" .
Please give a suggesstion in this case. Thanks in advance for your reply.

 

[arno]

maybe you should post your makro.

arno

 

[Avadivelan TCS]

Sub CleanCR()
Selection.Replace What:=Chr(10), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

 

[Dave Peterson]

Modified from a previous post:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = vbLf
AfterStr = " "

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

 

[Avadivelan TCS]

Hi Dave Peterson,

Good Morning , Thanks a lot for your reply. that code is working well.
In that i am having one doubt, what is the use of vbLf ..?
And one more doubt: If the cell value starts with '987,'789,'12036,'1364, etc
I want to replce this character ( ' ) to space. please help me in this.
Thanks in advance.

 

[Dave Peterson]

vblf = chr(10) = alt-enter

If you're converting a range of text numbers to number numbers, you can get lots
all at one time--no need to loop through the cells.

Select an empty cell
edit|copy
select the range to fix
edit|paste special|add

Record a macro if you need the code.

This'll be lots quicker than cycling through the cells.