Excel VBAを使うと、セル内のデータから空白を自動的に削除したり、空のセルを上詰めしてデータを整理することが可能です。大量のデータや自動化が求められる作業において、空白セルの処理を効率化するためにVBAを活用する方法に役立ちます。Excel VBAで空白を削除し、データを上詰めする方法について説明します。
目次
Excel VBAで空白を削除してデータを上詰めする方法
空白を削除して上詰めするシナリオ
例えば、Excelのシートに以下のようなデータがあるとします。
A列
1. りんご
2. （空白）
3. ばなな
4. （空白）
5. みかん
この状態では、データの間に空白セルがあり整理されていません。空白セルを削除して、データを詰めたい場合、VBAを使うことで迅速に処理が可能です。
空白セルを削除して上詰めする基本的な方法
空白セルを削除して上詰めするためには、範囲内のデータをチェックし、空白のセルがあった場合、その下にあるデータを上に移動させる必要があります。
【使用例: 空白セルを削除してデータを上詰めする】
Sub DeleteBlanksAndShiftUp()
Dim lastRow As Long
Dim ws As Worksheet
Dim i As Long
' 対象のシートを設定
Set ws = ThisWorkbook.Sheets("Sheet1")
' A列の最終行を取得
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' ループして空白セルを削除し、データを上詰め
For i = lastRow To 1 Step -1
If ws.Cells(i, 1).Value = "" Then
ws.Cells(i, 1).Delete Shift:=xlUp
End If
Next i
End Sub
シートのA列を対象にして、空白セルを削除し、下のデータを上に詰めます。最終行を取得し、下から上に向かって処理を行うことで、空白を削除しても正しく上詰めが行われます。
空白以外の見えない文字（スペースや改行）の削除
空白セルの中には、スペースや改行など、見えない文字が含まれている場合もあります。これらの見えない文字を削除して上詰めを行いたい場合、以下のようにスペースや改行を削除する処理を追加します。
【使用例: 見えない空白（スペース、改行）を削除して上詰めする】
Sub RemoveSpacesAndShiftUp()
Dim lastRow As Long
Dim ws As Worksheet
Dim i As Long
Dim cellValue As String
' 対象のシートを設定
Set ws = ThisWorkbook.Sheets("Sheet1")
' A列の最終行を取得
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' ループしてスペースや改行を削除し、データを上詰め
For i = lastRow To 1 Step -1
cellValue = Trim(Replace(Replace(ws.Cells(i, 1).Value, vbCrLf, ""), vbTab, ""))
If cellValue = "" Then
ws.Cells(i, 1).Delete Shift:=xlUp
End If
Next i
End Sub
Trim関数とReplace関数を組み合わせて、セル内の空白、改行、タブを削除しています。これにより、空白文字や見えない文字が原因でセルが空白と認識されない問題が解決されます。
【VBA】スペースを一括で削除【Replace関数：Trim関数】
空白セルを特定範囲内で削除して上詰めする
特定の範囲内でのみ空白セルを削除したい場合もあるでしょう。次のコードでは、A1からA10までの範囲内で空白セルを削除し、上詰めを行います。
【使用例: 特定範囲内の空白セルを削除して上詰め】
Sub DeleteBlanksInRangeAndShiftUp()
Dim ws As Worksheet
Dim cell As Range
Dim rng As Range
' 対象のシートを設定
Set ws = ThisWorkbook.Sheets("Sheet1")
' 処理対象の範囲を設定
Set rng = ws.Range("A1:A10")
' ループして範囲内の空白セルを削除し、データを上詰め
For Each cell In rng
If cell.Value = "" Then
cell.Delete Shift:=xlUp
End If
Next cell
End Sub
指定された範囲内でのみ空白セルを削除し、データを上詰めします。範囲の大きさや対象の列を自由に変更することが可能です。
【注意点】
空白セルは連続している場合は、最初のセルは削除されるが、空白セルが上詰めされるので、確認されて残ります。
連続しない時に有効な方法になります。
空白セルが多くても効率よく上詰めする方法
大量の空白セルが含まれている場合、1つずつ上詰めする方法では効率が悪くなる可能性があります。次のコードでは、フィルター機能を使って空白を素早く削除し、データをまとめて上詰めします。
【使用例: フィルターを使用して空白セルを削除して上詰め】
Sub FilterAndDeleteBlanks()
Dim ws As Worksheet
Dim lastRow As Long
' 対象のシートを設定
Set ws = ThisWorkbook.Sheets("Sheet1")
' A列の最終行を取得
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' フィルターを適用して空白セルを非表示
With ws.Range("A1:A" & lastRow)
.AutoFilter Field:=1, Criteria1:="="
' フィルターで表示されている空白セルを削除
On Error Resume Next
.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
On Error GoTo 0
End With
' フィルターを解除
ws.AutoFilterMode = False
End Sub
フィルター機能を使用して空白セルを一気に非表示にし、その後に表示されている空白セルを削除しています。この方法は、大量のデータを扱う場合に効果的です。
【注意点】
表示されているセルを削除するので、ヘッダー情報も一緒に削除されます。２行目以降のデータのみが残ります。
ヘッダー情報も残したい場合は「.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp」⇒「ws.Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp」に書き換えて削除範囲を指定することで、ヘッダー情報を残すことができます。
【VBA】セル・行・列・シート・ファイルの削除（Delete・Killステートメント）
注意点
- 削除の対象範囲を確認する: 上詰め処理は、指定した列や範囲に対して実行されるため、意図した範囲で処理を行うことが重要です。処理対象外のデータが削除されないように注意してください。
- 処理後のデータ確認: 上詰め処理を行った後、意図せずに削除されたデータがないか、データの並びに問題がないかを必ず確認するようにしましょう。
まとめ
Excel VBAを使用して空白セルを削除し、データを上詰めすることで、データの整理や効率化が可能です。この記事で紹介した方法を応用することで、見えない空白文字を削除したり、特定の範囲内でのみ処理を行ったりと、さまざまなシナリオに対応できます。