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

自力で老眼回復

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

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

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

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

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

レクタングル大336×280

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

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

この改行コードですが、

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

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

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

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

ユーザーフォームの作成

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

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

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

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

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

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

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

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

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

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

プログラムコードの解説

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

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

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を学びながら進めることができます。

  1. エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法 (本記事です)
  2. エクセルVBAでセル内の文字列を操作して全角文字を半角に一括変換する方法
  3. エクセルVBAでチェックボックスのONOFFでボタン表示を変える方法
  4. エクセルVBAでセル内文字列の前後の空白をまとめて一括で削除する方法
  5. エクセルVBAのReplace関数でセルの文字列内の空白をまとめて一括削除する
  6. エクセルVBAのCountA関数でシート内の空白行をまとめて一括で削除する方法
  7. エクセルVBAで空白行を削除しないでまとめて一括非表示で隠す方法
  8. エクセルVBAで条件付き書式を使わず1行おきに色を付ける方法
  9. エクセルVBAで集計や並び替えを使わず1行おきに空白行を一括で挿入する方法
  10. エクセルVBAで同じデータが切り替わる項目ごとに空白行を挿入する方法
レクタングル大336×280
レクタングル大336×280

シェアする

  • このエントリーをはてなブックマークに追加

フォローする