Selection.Replace size limitation
 

I have Excel 2002, SP-2.

If the text cell selected is big in length, the code below does not work.

Sub Trim10()
Dim cell As Range
Selection.Replace What:=Chr(10), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

The longest length which worked was 945 but some which are less than that
also did not work for some reason. Under 900 and it looks as thought it
always works. I can confirm that the ones which haven't worked do contain
Chr(10).
Is there anything I can do to fix this size limitation?

Thanks
--
http://www.takesyourfancy.co.uk/ - Great products and great prices for all
the family

Selection.Replace size limitation
 

I think you have to work around it.

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!

Stopfordian wrote:

I have Excel 2002, SP-2.

If the text cell selected is big in length, the code below does not work.

Sub Trim10()
Dim cell As Range
Selection.Replace What:=Chr(10), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

The longest length which worked was 945 but some which are less than that
also did not work for some reason. Under 900 and it looks as thought it
always works. I can confirm that the ones which haven't worked do contain
Chr(10).
Is there anything I can do to fix this size limitation?

Thanks
--
http://www.takesyourfancy.co.uk/ - Great products and great prices for all
the family

--

Dave Peterson

Selection.Replace size limitation
 

Thanks very much Dave, it worked a treat.

--
http://www.takesyourfancy.co.uk/ - Great products and great prices for all
the family

I think you have to work around it.

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!

Stopfordian wrote:

I have Excel 2002, SP-2.

If the text cell selected is big in length, the code below does not work.

Sub Trim10()
Dim cell As Range
Selection.Replace What:=Chr(10), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

The longest length which worked was 945 but some which are less than that
also did not work for some reason. Under 900 and it looks as thought it
always works. I can confirm that the ones which haven't worked do
contain
Chr(10).
Is there anything I can do to fix this size limitation?

Thanks
--
http://www.takesyourfancy.co.uk/ - Great products and great prices for
all
the family

--

Dave Peterson