Excel のデータ処理では、セル内の余分なスペースを削除したい場合があります。大量のデータを処理する際に、手動でスペースを削除するのは手間がかかります。
Excel VBA を使用してシート全体のスペースを一括で削除する方法について、基本から活用方法までを解説します。
スペースの種類
Excel で扱うスペースには以下のような種類があります。
- 前後のスペース: セル内の文字列の先頭や末尾にあるスペース。
- 中間のスペース: 文字列の途中にある余分なスペース。
前後のスペースを削除する
セル内の前後のスペースを削除するには、VBA の Trim 関数を使用します。【VBA】スペースを一括で削除【Replace関数：Trim関数】
【シート全体の前後のスペースを削除】
Sub RemoveLeadingAndTrailingSpaces()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
' シート内のすべてのセルをループ
For Each cell In ws.UsedRange
If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
cell.Value = Trim(cell.Value)
End If
Next cell
MsgBox "前後のスペースを削除しました！"
End Sub
解説
Trim関数は、文字列の前後のスペースを削除します。- 空のセルや数値型のセルを処理しないように条件分岐を追加しています。
すべてのスペース（前後および中間）を削除する
セル内のすべてのスペースを削除するには、Replace 関数を使用します。
【シート全体のすべてのスペースを削除】
Sub RemoveAllSpaces()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
' シート内のすべてのセルをループ
For Each cell In ws.UsedRange
If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
cell.Value = Replace(cell.Value, " ", "")
End If
Next cell
MsgBox "すべてのスペースを削除しました！"
End Sub
解説
Replace(cell.Value, " ", "")を使用して、セル内のすべてのスペースを空文字に置き換えます。- この方法は、前後のスペースだけでなく、中間のスペースも削除します。
特定の範囲内だけでスペースを削除
シート全体ではなく、特定の範囲内でスペースを削除したい場合は、以下のコードを使用します。
【特定の範囲内でスペースを削除】
Sub RemoveSpacesInRange()
Dim ws As Worksheet
Dim targetRange As Range
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set targetRange = ws.Range("A1:C10") ' 対象範囲を指定
' 範囲内のすべてのセルをループ
For Each cell In targetRange
If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
cell.Value = Replace(cell.Value, " ", "")
End If
Next cell
MsgBox "範囲内のスペースを削除しました！"
End Sub
解説
targetRangeに対象範囲を指定して、その範囲内だけを処理します。- 必要に応じて範囲を変更してください。
半角スペースと全角スペースを両方削除する
日本語のデータでは、半角スペースだけでなく全角スペースも含まれる場合があります。この場合、半角スペースと全角スペースを両方削除する必要があります。
【半角・全角スペースを削除】
Sub RemoveAllSpacesIncludingFullWidth()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
' シート内のすべてのセルをループ
For Each cell In ws.UsedRange
If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
cell.Value = Replace(Replace(cell.Value, " ", ""), " ", "")
End If
Next cell
MsgBox "半角および全角スペースを削除しました！"
End Sub
解説
- 内側の
Replace(cell.Value, " ", "")で半角スペースを削除。 - 外側の
Replace(..., " ", "")で全角スペースを削除。
空白セルを削除する
スペース削除ではなく、空白セルを削除したい場合のコードも紹介します。
【空白セルを削除】
Sub DeleteEmptyCells()
Dim ws As Worksheet
Dim targetRange As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set targetRange = ws.UsedRange
' 空白セルを削除
targetRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
MsgBox "空白セルを削除しました！"
End Sub
解説
SpecialCells(xlCellTypeBlanks)を使用して空白セルを特定します。【VBA】特定のセル（空白セル・数式セル・エラーセル）を見つけ出す：SpecialCells メソッド- 空白セルを削除し、上方向にセルをシフトします。
注意点
数値や数式が含まれるセルの扱い
数値や数式が含まれるセルに対してスペース削除処理を行うと、意図しない結果を招く場合があります。適切に条件分岐を使用してください。データのバックアップ
VBA でデータを一括処理する場合、データの損失を防ぐために、事前にバックアップを取ることをおすすめします。パフォーマンスの最適化
処理対象のデータが多い場合、処理速度が遅くなることがあります。Application.ScreenUpdating = Falseを使用して画面更新を停止することで処理を高速化できます。【VBA】Application.ScreenUpdatingプロパティの使用方法と特徴
まとめ
Excel VBA を使えば、シート全体のスペースを簡単に削除できます。用途に応じて以下の方法を使い分けましょう。
Trim関数: 前後のスペースを削除。Replace関数: すべてのスペース（半角・全角）を削除。- 特定の範囲のみ処理: 必要な範囲だけを対象に処理。