How to delete blank or empty cells using VBA Excel – debugvba.com

delete empty cells vba excel

Objective

To delete blank or empty cells using VBA Excel.

Approach

Here we had data in column A of sheet1, and we had blank cells in this column, which were A4, A8, A9. First, we have selected the sheet1, since we had data in this sheet.

So, in order to delete cells, we have found the row number of last populated row and then we looped through each cell. If the cell is empty, then we delete the cell and move the cells at the bottom to shift up.

Here we have used the for loop in the reverse order.

Code

Sub DeleteEmptyCells()

Dim LastRow As Long
Dim RowNum As Long

Sheets("sheet1").Select


'Find the last row in column A
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Loop through rows to find the empty cells

For RowNum = LastRow To 2 Step -1

'Check if cell is blank
If Range("A" & RowNum).Value = "" Then
Range("A" & RowNum).Select
'Delete the cell
Selection.Delete Shift:=xlUp
End If

Next RowNum

End Sub

 

 

delete empty cells vba excel

 

Post you may like

Find and replace string in a column using VBA Excel 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.delete