ReplaceメソッドとReplace関数

Replaceメソッドは以前の記事
『 セル内の改行を削除する 』において使用しました。

このReplaceメソッドを使って
セル内の文字列の改行を削除しようとしたら
次のようなエラーが出ることがありました。

これはExcel2003の場合は、
置換後のセル内の文字数が911を超えた場合置換できない現象のようです。
参照： [XL2003] 置換後のセル内の文字数が911を超えた場合置換できない

試しにVBAでこの置換できない現象を確かめてみました。

コードはこちら

Sub macro110508a()
'置換の文字数について
'i=912でエラーになる

    Dim i As Integer
    i = 900
    For i = 912 To 10000
        Range("A1") = WorksheetFunction.Rept("a", i)
        '次がi=912でエラーになる
        Range("A1").Replace "a", "b"
    Next i
   
End Sub

Excel2000においても、
置換後のセル内の文字数が911を超えた場合
置換できませんでした。

さてこういうときはどうすればいいかというと、
題にもあるようにReplace関数を使えば
このような場合も改行を削除できます。

コードはこちら

Sub macro110508b()
'文字列の改行を削除する
'セルA1
    Dim Str As String
   
    Str = Range("A1")
    Str = Replace(Str, Chr(10), "")
    Range("A1") = Str

End Sub

Replaceメソッドを使う場合は
次のようにすると複数のセルを一度に処理できました。

Range("A1:C10").Replace Chr(10), ""

Replace関数はセルの内容を文字列にしてから処理するので
一度に１つのセルしか処理できません。
複数のセルを処理したいときは
For Eachステートメントを使います。

コードはこちら

Sub macro110508c()
'文字列の改行を削除する
'ActiveSheet.UsedRange

    Dim c As Object
    Dim Str As String
    For Each c In ActiveSheet.UsedRange
        Str = c
        Str = Replace(Str, Chr(10), "")
        c = Str
    Next c
   
End Sub

上のコードとても遅いです。

範囲を明確に指定したほうがいいかもしれません。
範囲を指定したいときは

ActiveSheet.UsedRange

の箇所を

ActiveSheet.Range("A1:C10")

などとしてください。