How to Delete Empty Rows and Columns in Excel VBA (4 Methods)

While working with VBA in Excel, we often need to delete empty rows and columns. In this article, I’ll show you how you can delete rows and columns in Excel VBA both when at least one cell is empty and all the cells are empty.

Delete Empty Rows and Columns in Excel VBA (Quick View)

Sub Delete_Rows_with_All_Empty_Cells()

SheetName = "Sheet2"

DataSet = "B3:F15"

Empty_Columns = 0

For i = Worksheets(SheetName).Range(DataSet).Rows.Count To 1 Step -1
    For j = 1 To Worksheets(SheetName).Range(DataSet).Columns.Count
        If Worksheets(SheetName).Range(DataSet).Cells(i, j) = "" Then
            Empty_Columns = Empty_Columns + 1
        End If
    Next j
    If Empty_Columns = Worksheets(SheetName).Range(DataSet).Columns.Count Then
            Worksheets(SheetName).Range(DataSet).Cells(i, 1).EntireRow.Delete
    End If
    Empty_Columns = 0
Next i

End Sub

How to Delete Empty Rows and Columns in Excel VBA: 4 Easy Methods

Here we’ve got a worksheet called Sheet1 that contains a data set with the Client Names, Account Numbers, Genders, Account Types, and Contact Numbers of some clients of a bank.

But unfortunately, some client data are missing in the date set. That is, some cells in the data set are empty.

Our objective today is to delete the rows and columns with empty cells from this data set with Excel VBA.

1. Delete Rows in Excel VBA When at Least One Cell Is Empty

First of all, we’ll the rows from this data set when at least one cell is empty.

The VBA code is very simple here. It’s as follows:

⧭ VBA Code:

Sub Delete_Rows_with_At_Least_One_Empty_Cell()

SheetName = "Sheet1"

DataSet = "B3:F15"

For i = Worksheets(SheetName).Range(DataSet).Rows.Count To 1 Step -1
    For j = 1 To Worksheets(SheetName).Range(DataSet).Columns.Count
        If Worksheets(SheetName).Range(DataSet).Cells(i, j) = "" Then
            Worksheets(SheetName).Range(DataSet).Cells(i, j).EntireRow.Delete
            Exit For
        End If
    Next j
Next i

End Sub

⧭ Output:

Run the code while changing the inputs according to your need (SheetName and DataSet). It’ll remove all the rows that contain at least one empty cell from this data set.

2. Delete Columns in Excel VBA When at Least One Cell Is Empty

Next, we’ll delete the columns from this data set when at least one cell is empty.

The VBA code will be as follows:

⧭ VBA Code:

Sub Delete_Columns_with_At_Least_One_Empty_Cell()

SheetName = "Sheet1"

DataSet = "B3:F15"

For i = Worksheets(SheetName).Range(DataSet).Columns.Count To 1 Step -1
    For j = 1 To Worksheets(SheetName).Range(DataSet).Rows.Count
        If Worksheets(SheetName).Range(DataSet).Cells(j, i) = "" Then
            Worksheets(SheetName).Range(DataSet).Cells(j, i).EntireColumn.Delete
            Exit For
        End If
    Next j
Next i

End Sub

⧭ Output:

Run the code while changing the inputs according to your needs (SheetName and DataSet). It’ll remove all the columns that contain at least one empty cell from this data set.

3. Delete Rows in Excel VBA When All the Cells Are Empty

Now we’ve modified our data set a bit. There is a new worksheet called Sheet2 that contains the same data set, along with some rows and columns that are completely empty.

First, we’ll delete the rows from this data set when all the cells are empty.

The VBA code will be:

⧭ VBA Code:

Sub Delete_Rows_with_All_Empty_Cells()

SheetName = "Sheet2"

DataSet = "B3:F15"

Empty_Columns = 0

For i = Worksheets(SheetName).Range(DataSet).Rows.Count To 1 Step -1
    For j = 1 To Worksheets(SheetName).Range(DataSet).Columns.Count
        If Worksheets(SheetName).Range(DataSet).Cells(i, j) = "" Then
            Empty_Columns = Empty_Columns + 1
        End If
    Next j
    If Empty_Columns = Worksheets(SheetName).Range(DataSet).Columns.Count Then
            Worksheets(SheetName).Range(DataSet).Cells(i, 1).EntireRow.Delete
    End If
    Empty_Columns = 0
Next i

End Sub

⧭ Output:

Run the code while changing the inputs according to your needs (SheetName and DataSet). It’ll remove all the rows that contain all empty cells from this data set.

4. Delete Columns in Excel VBA When All the Cells Are Empty

Next, we’ll delete the columns from this data set when all the cells are empty.

The VBA code will be:

⧭ VBA Code:

Sub Delete_Columns_with_All_Empty_Cells()

SheetName = "Sheet2"

DataSet = "B3:H15"

Empty_Rows = 0

For i = Worksheets(SheetName).Range(DataSet).Columns.Count To 1 Step -1
    For j = 1 To Worksheets(SheetName).Range(DataSet).Rows.Count
        If Worksheets(SheetName).Range(DataSet).Cells(j, i) = "" Then
            Empty_Rows = Empty_Rows + 1
        End If
    Next j
    MsgBox Empty_Rows
    If Empty_Rows = Worksheets(SheetName).Range(DataSet).Rows.Count Then
            Worksheets(SheetName).Range(DataSet).Cells(1, i).EntireColumn.Delete
    End If
    Empty_Rows = 0
Next i

End Sub

⧭ Output:

Run the code while changing the inputs according to your needs (SheetName and DataSet). It’ll remove all the columns that contain all empty cells from this data set.

Things to Remember

Here we’ve used a backward for-loop through the rows and columns while deleting the rows and columns. For deleting rows, we started from the last row and ended in the first row of the data set. Similar to deleting columns.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Delete Empty Rows and Columns.xlsm