Rifat Hassan
In this article, I will show you how to delete row with VBA macro if the cell is blank in Excel. You’ll learn all the steps from opening the VBA window to extracting out the final output, with proper examples and illustrations. So, without any delay, let’s start the discussion.
Before starting the procedure, let’s have a quick look at the steps we are using to delete a row if the cell is blank.
Download Practice Workbook
Step-by-Step Procedure to Delete Row with VBA Macro If Cell Is Blank in Excel
Here, we’ve got a data set with the Names of some Candidates, and their Marks in Preliminary, Written, and Viva of the recruitment test of a company called Jupyter Group.
In this dataset, we can see that some of the cells in this data set are blank. This means that candidate didn’t appear in that particular test. So, our objective is to delete the rows with blank cells from this data set.
Step 1: Open VBA Window
Firstly, we need to open the VBA window so that we can enter Macro. In the Macro mode, we can insert the VBA code.
➤ To do so, press ALT+F11 on your keyboard.
➤ As a result, it will open the VBA window.
Step 2: Enter New Module
In this step, we need to insert a module where we will type our code.
➤ In the beginning, go to Insert >> Module option in the VBA toolbar.
➤ Then, it will insert a new module called Module 1.
Step 3: Insert VBA Code in VBA Module
In this step, we will insert the VBA code in the module. As the outcome of the code, Excel will delete the row if the cell is blank.
➤ Now, insert the following VBA code in the module.
Sub Delete_Rows_with_Blank_Cells()
On Error GoTo Message
Column_Numbers = InputBox("Enter the Number of the Columns with the Blank Cells: ")
Column_Numbers = Split(Column_Numbers, " ")
Dim Count As Integer
Count = 0
For i = 1 To Selection.Rows.Count
For j = 0 To UBound(Column_Numbers)
If Selection.Cells(i, Int(Column_Numbers(j))) = "" Then
Selection.Cells(i, 1).EntireRow.Delete
i = i - 1
Count = Count + 1
Exit For
End If
Next j
If Count >= Selection.Rows.Count Then
Exit For
End If
Next i
Exit Sub
Message:
MsgBox "Please Enter Valid Integers as the Column Numbers Separated by Spaces."
End Sub
Code Breakdown
Step 4: Save Macro-Enabled Workbook
Before running the code, we need to save the workbook in the macro-enabled mode.
➤ Now, save the workbook as Excel-Macro Enabled Workbook.
Step 5: Select Data Set to Delete Row with Macro in Excel
In this step, we will select the data range where the code will be applicable.
➤ Firstly, come back to your Excel Worksheet.
➤ After that, in the worksheet, select the data set from where you want to delete the rows with blank cells (Without the Column Headers).
Step 6: Run Macro to Delete Row
Now, we will run Macro to delete rows if the cell is blank.
➤ In the following step, press ALT+F8 on your keyboard. A dialogue box called Macro will open.
➤ Then, select Delete_Rows_with_Blank_Cells (The name of the Macro) and click on Run.
Step 7: Enter Inputs in Input Box to Delete Row If Cell Is Blank
In this step, we will take input. The inputs will select the rows for deleting the rows if blank cells are found.
➤Here, you will get an Input Box asking you to enter the numbers of the columns from where you want to remove the blank cells.
➤ So, let’s delete candidates who didn’t appear in the Preliminary and Written exams.
➤Therefore, we input 2 3 (Separated by a space).
⧪ Final Output
Now, we will get the final result.
➤ Finally, click on OK.
➤As a result, it will delete all the rows that have blank cells in columns 2 and 3.
Read More: Formula to Remove Blank Rows in Excel (5 Examples)
Things to Remember
- Here, you can use this code to delete rows with blank cells from a single column. Just enter the number of the column in the Input Box.
- So, you can also use this code to delete the blank cells from the whole data set. Enter the numbers of all the columns of the data set. For example, 1 2 3 4.
Conclusion
In this article, we have demonstrated how to apply Excel Macro to delete the row if the cell is blank. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out our website ExcelDemy. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
