To delete blank or empty cells using VBA Excel.
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.
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
Post you may like