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
```

**Table of Contents**hide

**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 need (**SheetName **and** DataSet**). Itâ€™ll remove all the columns that contain at least one empty cell from this data set.

**Read More: ****How to Find Difference Between Rows and Columns in Excel**

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

**Read More: ****How to Lock Column Width and Row Height in Excel**

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

**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 in the comment section.

**Related Articles**

**Excel Macro: Convert Multiple Rows to Columns****Excel VBA to Set Range Using Row and Column Numbers****[Fixed!] Missing Row Numbers and Column Letters in Excel****How to Switch Rows and Columns in Excel Chart****Excel VBA: Get Row and Column Number from Cell Address****How to Switch Rows and Columns in Excel****How to Convert Multiple Rows to Columns in Excel****[Fixed!] Rows and Columns Are Both Numbers in Excel****Excel VBA: Set Range by Row and Column Number****How to Add Rows and Columns in Excel**