Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

VBA Code to Delete Empty Rows and Columns in Excel VBA


Download Practice Workbook

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


4 Methods to Delete Empty Rows and Columns in Excel VBA

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.

Data Set to Delete Empty Rows and Columns in Excel VBA

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

VBA Code to Delete Empty Rows and Columns in Excel VBA

⧭ 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.

Read More: How to Remove Blank Rows in Excel Pivot Table (4 Methods)


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

VBA Code to Delete Empty Rows and Columns in Excel VBA

⧭ Output:

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

Delete Empty Rows and Columns in Excel VBA Output

Read More: How to Delete Blank Cells and Shift Data Left in Excel (3 Methods)


Similar Readings


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

VBA Code to Delete Empty Rows and Columns in Excel VBA

⧭ Output:

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

Delete Empty Rows and Columns in Excel VBA Output

Read More: How to Remove Blank Rows in Excel (7 Methods)


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 need (SheetName and DataSet). It’ll remove all the columns that contain all empty cells from this data set.

Read More: How to Remove Partial Data from Multiple Cells in Excel (6 Ways)


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.


Conclusion

So these are the ways to delete empty rows and columns in Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo