エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法

こんにちは～。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も「時間クリエーター」としてバシバシ時短に取り組んでます！

今回から全10回に渡って、エクセルのデータ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。第1回目は改行を削除する機能を実装していきます。

さてみなさんはワークシートの複数のセル内にある改行を、一度に消したいって思ったことありませんか？

エクセル内では改行が入っていることに問題はないけど、データを別のアプリケーションに取り込んだり、データベースに登録したりするときに改行が邪魔になることがあります。

そんな時に改行を一括で削除できるツールがあったら便利ですよね。今回はエクセルのVBAを使ってセル内の文字列に含まれる改行を削除する方法の紹介です。

1 エクセルの改行コードとは
2 改行削除プログラムの作成
3 プログラムコードの解説
4 改行削除プログラムの動作確認
5 まとめ
6 [連載記事全10回] エクセルVBAで文字列変換ツールを作る

エクセルの改行コードとは

ワークシートのセル内で改行するときは「Altキー+Enterキー」を押して改行すると思います。この時、見た目では文字列が改行されただけに見えますが、実際には目に見えない「改行コード」が入っています。

この改行コードですが、

WindowsではvbCrLf、Excelのセル内改行はvbLfを使います。

エクセルの「置換」機能を使って、検索する文字に「Ctrl + J」と入力して改行を削除する方法や、CLEAN関数を使う方法などがありますが、エクセルVBAでプログラムを作っておくと何かと便利です。あとで他の文字列変換などと組み合わせるなどの応用が利くからです。

改行削除プログラムの作成

新規にエクセルファイルを作成しファイル名を「一括変換.xls」としてデスクトップに保存します。

ユーザーフォームの作成

それではユーザーフォームを挿入しコマンドボタンを配置していきます。

開発タブからVisual Basic Editorを開きます。

VBAProject、挿入、ユーザーフォームの順でクリックします。

ツールボックスからコマンドボタンをクリックして、ユーザーフォーム上の適当な位置でドラッグします。するとコマンドボタンができます。

コマンドボタンを右クリックしてプロパティをクリックします。プロパティウィンドウのCaptionを「改行削除」に変更します。

サンプルコードを貼り付ける

改行削除ボタンをダブルクリックしてフォームモジュールに以下のコードを貼り付けます。

'改行削除
Private Sub CommandButton1_Click()

Dim tmp
Dim Col As Long
Dim rowStart As Long
Dim rowEnd As Long
Dim cellData As String
Dim y As Long
Dim codeNewLine As String

    '処理開始行
    rowStart = 2
    
    '最終行を取得
    With ActiveSheet.UsedRange
     rowEnd = .Rows(.Rows.Count).Row
    End With
    
    tmp = Application.InputBox("列を選択して下さい", "列の選択", Type:=8).Address
    Range(tmp).Select
    '選択した列番号
    Col = Selection.Column
                       
    For y = rowStart To rowEnd
        cellData = Cells(y, Col).Value
        '改行コード
        codeNewLine = "" &amp; vbLf &amp; ""
        '改行コードを削除する
        cellData = Replace(cellData, codeNewLine, "", , , vbTextCompare)
        Cells(i, Col).Value = cellData
    Next y

End Sub

'改行削除

Private Sub CommandButton1_Click()

Dim tmp

Dim Col As Long

Dim rowStart As Long

Dim rowEnd As Long

Dim cellData As String

Dim y As Long

Dim codeNewLine As String

'処理開始行

rowStart = 2

'最終行を取得

With ActiveSheet.UsedRange

rowEnd = .Rows(.Rows.Count).Row

End With

tmp = Application.InputBox("列を選択して下さい", "列の選択", Type:=8).Address

Range(tmp).Select

'選択した列番号

Col = Selection.Column

For y = rowStart To rowEnd

cellData = Cells(y, Col).Value

'改行コード

codeNewLine = "" & vbLf & ""

'改行コードを削除する

cellData = Replace(cellData, codeNewLine, "", , , vbTextCompare)

Cells(i, Col).Value = cellData

Next y

End Sub

ワークシートに起動ボタンを作る

ワークシート上の適当な位置にコマンドボタンを配置します。表示名は「起動」などとします。

起動ボタンをダブルクリックしてシートモジュールに以下のコードを書きます。

Private Sub CommandButton1_Click()

    Load UserForm1
    UserForm1.Show vbModeless
    
End Sub

Private Sub CommandButton1_Click()

Load UserForm1

UserForm1.Show vbModeless

End Sub

プログラムコードの解説

重要なポイントに要点を絞って説明していきます。

使用されたセル範囲の最終行の取得

With ActiveSheet.UsedRange
Row_End = .Rows(.Rows.Count).Row
End With

UsedRangeプロパティはワークシートで使用されたセルの範囲を返します。
.Rows(.Rows.Count).Rowは最終行を取得します。つまりワークシートで使用中にセルの中から最終行を取得することになります。

この時、使用中のセルとはデータが入力されているセルだけでなく、罫線などの書式を設定してたセルも含まれます。

最終行を取得する方法はいくつかありますが、私はこの方法が一番使いやすいと思っています。

InputBoxから列番号を取得

tmp = Application.InputBox(“列を選択して下さい”, “列の選択”, Type:=8).Address

Typeは引数で、InputBoxに入力できるデータ型を特定することができます。ここで指定している8は「Rangeオブジェクト」です。「Address」プロパティを指定することで「$E:$E」のようなRangeオブジェクトの番地を取得できます。

InputBoxで取得したRangeオブジェクトを選択します。

Range(tmp).Select

選択したRangeオブジェクトから列番号を取得します。

Col = Selection.Column

Replace関数で改行を削除する

codeNewLine = “” & vbLf & “”
cellData = Replace(cellData, codeNewLine, “”, , , vbTextCompare)

エクセルの改行をコードである「vbLf」をReplace関数で削除します。コードが見づらくなるので変数codeNewLineに改行コードを入れています。

改行削除プログラムの動作確認

ワークシートの起動ボタンを押します。フォームが表示されたら改行削除ボタンを押します。

改行を削除したい列をクリックします。InputBoxのOKボタンを押します。

改行が削除されました。

まとめ

いかがでしたか？今回の内容をまとめます。

エクセルの改行コードはvbLfを使う
Inputboxで選択した列番号を取得する
Replace関数で改行コードであるvbLfを削除する

以上おつかれさまでした～。

[連載記事全10回] エクセルVBAで文字列変換ツールを作る

全角半角の変換をしたり、空白行や改行を削除したり。データ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。1つの記事で1機能ずつ追加していきますので、少しずつVBAを学びながら進めることができます。