【Excelマクロ】空白(スペース・タブ)と改行の全削除

Excelマクロ

 Excelでデータを扱う際に、データの前後に空白や改行があると、Excelがデータを数値として認識できずその後の処理ができない。

 具体的な例を挙げると、表からグラフを作成する際にはデータを数値として読み取れないため、正しいグラフを描画できない。

 そのためデータの前後にある空白や改行は削除する必要があるが、データ数が数百にも及ぶ場合に1つ1つ手作業で空白や改行を消すのは現実的ではない。

 

 そこでマクロの出番である。

 マクロを使えば、セル内のすべてのスペース、タブ、改行を一瞬で削除することが可能だ。

 本記事では実際に空白と改行の全削除をするマクロのサンプルを紹介し、コードを詳しく解説していく。

Microsoft Excel 2019の利用を想定しています。

サンプル

 今回使うサンプルは下記ボタンから入手可能である。

 まず、2つあるファイルの内「data2-1.xlsx」がサンプルデータファイルである。
 データの各所にわざとスペース、タブ、改行を設けてある。

 もう1つのファイル「exp2_comp.xlsm」にマクロが保存されており、ここで空白と改行の削除が可能である。

 

 使い方は、「exp2_comp.xlsm」のSheet1にデータを直接貼り付け、データ部分を選択した状態でボタンを押すだけである。

 選択された領域の空白と改行がいっぺんに削除される。

コード詳説

 実際に保存されているマクロが下記である。

Sub 空白の全削除()

Dim cls As Range
Dim spa As String

For Each cls In Selection.Cells
    spa = cls.Value
    spa = Replace(spa, " ", "")     '半角スペース削除
    spa = Replace(spa, " ", "")    '全角スペース削除
    spa = Replace(spa, vbTab, "")   'タブ削除
    spa = Replace(spa, vbLf, "")    'セル内改行削除
    cls.Value = spa
Next

End Sub

 

For Each 変数 In コレクション ~ Next

繰り返し処理の構文であり、指定したコレクション内の要素を1つずつ変数に代入し、「Next」までの間の処理を繰り返していく。

 コレクションとは、同じ種類のものの集合体と考えればよい。

 今回はシート内で選択されたセルの集まり「Selection.Cells」がコレクションであり、選択されたセル1つ1つを変数「cls」に代入して順番に処理を進める。

 

Replace(置換前の文字列 , 置換対象の文字列 , 置換後の文字列)

文字列を置換する関数である。

 例えば「Replace(“ABC” , “A” , “D”)」の場合、置換前の文字列「ABC」の内の「A」が「D」に置換されて「DBC」となる。

 置換後の文字列をダブルクォーテーション2つ「””」にすれば、置換対象の文字列を消去することが可能になる。

 

 よって

For Each cls In Selection.Cells
    spa = cls.Value
    spa = Replace(spa, " ", "")     '半角スペース削除
    spa = Replace(spa, " ", "")    '全角スペース削除
    spa = Replace(spa, vbTab, "")   'タブ削除
    spa = Replace(spa, vbLf, "")    'セル内改行削除
    cls.Value = spa
Next

は、まず変数「cls」に格納されたセルの文字列を変数「spa」に代入し、

 ①「spa」に代入された文字列のうち全角スペースを消して「spa」に代入し直す。
 ②「spa」に代入された文字列のうち半角スペースを消して「spa」に代入し直す。
 ③「spa」に代入された文字列のうちタブスペースを消して「spa」に代入し直す。
 ④「spa」に代入された文字列のうちセル内改行を消して「spa」に代入し直す。

を実施してすべての空白と改行を消し、その上でセル「cls」の値に代入し直す、という処理をシート内で選択されたすべてのセルに対して繰り返すコードになる。

終わりに

 上記の処理は、Excelシートを表示した状態でメニューから「置換」を選ぶことでも実行可能である。

 しかし複数種類の文字を置換、削除する場合は「置換」のウィンドウで複数回ボタンを押す必要がある。

 すでに置換対象の文字が決まっていて使用頻度が高いなら、1回の操作で処理が完了するマクロを作ってしまった方が効率は良いだろう。

 

 こんな感じで、今後も私が作ったマクロを随時紹介していこうと思う。

 

 END

 

 ※追記
 複数のデータファイルを1つのExcelファイルに取り込んで集約するマクロを書いた。

コメント

タイトルとURLをコピーしました