Rifat Hassan
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 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
