Macro to Delete Row in Excel If Cell is Blank

In this article, I’ll show you how to develop and run a Macro in Excel to delete a row if the cell is blank. You’ll learn all the steps from opening the VBA window to extracting out the final output, with proper examples and illustrations.


Macro to Delete Row in Excel If the Cell is Blank (Quick View)

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

VBA Code for Macro to Delete Row in Excel If the Cell is Blank


Download Practice Workbook


Steps to Delete Row with Macro in Excel If the Cell is Blank

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.

Data Set to Create Macro to Delete Row in Excel If the Cell is Blank

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.

Today our objective is to delete the rows with the blank cells from this data set.


Step 1: Opening the VBA Window to Delete Row with Macro in Excel

➤ First of all, press ALT+F11 on your keyboard.

➤ It will open the VBA window.

Opening VBA Window to Create Macro to Delete Row in Excel If the Cell is Blank

Step 2: Entering a New Module to Delete Row with Macro in Excel

➤ Then go to Insert > Module option in the VBA toolbar.

➤ It will insert a new module called Module 1.

Step 3: Inserting the VBA Code to Delete Row with Macro in Excel

➤ Next insert the following VBA code in the module.

VBA Code:

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

 

Note:

This code produces a Macro called Delete_Rows_with_Blank_Cells.

VBA Code for Macro to Delete Row in Excel If the Cell is Blank

Explanation of the Code:

  • Sub Delete_Rows_with_Blank_Cells() starts a new Macro called Delete_Rows_with_Blank_Cells.
  • On Error Goto Message instructs the code to go to the label called Message if it finds any error while executing.
  • Column_Numbers = InputBox(“Enter the Number of the Columns with the Blank Cells: “) asks the user to input the number of the columns where the blank cells are located.
  • There may be more than one such column. If there are more than one, you have to separate them by spaces.
  • Column_Numbers = Split(Column_Numbers, ” “) splits the inputs into an array of digits.
  • For example, if you input 2 3, it will split it into {2, 3}.
  • Dim Count As Integer declares an integer called Count.
  • Count=0 sets the initial value of Count as 0.
  • For i = 1 To Selection.Rows.Count starts an iteration for each row of the selected range.
  • For j = 0 To UBound(Column_Numbers) starts an iteration for each input column of each row of the selected range.
  • If Selection.Cells(i, Int(Column_Numbers(j))) = “” Then instructs that if any cell of the selected range is blank, then the next lines will be executed. Otherwise not.
  • Selection.Cells(i, 1).EntireRow.Delete instructs to delete the whole row if the cell is blank.
  • i = i – 1 reduces the value of i by 1. It, in fact, brings the iteration one step down.
  • This is due to the fact that when any row is deleted, the total number of rows reduces by one.
  • Count = Count + 1 increases the value of the variable Count by 1 if a row is deleted. It keeps track of the number of rows been deleted.
  • Exit For instructs the code to come out of the iteration through the columns of a row if any row is deleted.
  • This is quite natural. If any cell of a row is found blank, that row will be deleted. We needn’t examine the next cells of the row.
  • End If ends the If block.
  • Next j ends the for-loop of iterating through the columns of a particular row.
  • If Count >= Selection.Rows.Count Then initiates another If block, if the value of the variable Count is equal to the total number of selected rows.
  • Exit For instructs the code to exit out of the for-loop of iterating through each of the rows, if the number of the deleted rows becomes equal to the total number of rows.
  • This is quite important. The number of deleted rows can’t be greater than the total number of rows.
  • If you don’t write these lines, you will drown within a hole of infinite for-loops.
  • Exit Sub instructs the Macro to exit out of the code.
  • Message: starts a label called Message, which will be executed only if the code faces any error while running.
  • MsgBox “Please Enter Valid Integers as the Column Numbers Separated by Spaces.” instructs the user to input valid numbers as the columns numbers.
  • End Sub declares the end of the Macro.

Step 4: Saving the Macro-Enabled Workbook

➤ Then save the workbook as Excel-Macro Enabled Workbook.

Saving Workbook to Create Macro to Delete Row in Excel If the Cell is Blank

Step 5: Selecting the Data Set to Delete Row with Macro in Excel

➤ Come back to your worksheet.

➤ Then select the data set from where you want to delete the rows with blank cells (Without the Column Headers.)

Step 6: Running the Macro to Delete Row

➤ Next press ALT+F8 on your keyboard. A dialogue box called Macro will open.

➤ Select Delete_Rows_with_Blank_Cells (The name of the Macro) and click on Run.

Running Macro to Delete Row If Cell is Blank in Excel

Step 7: Entering the Inputs in the Input Box to Delete Row with Macro in Excel

➤ You will get an Input Box asking you to enter the numbers of the columns from where you want to remove the blank cells.

➤ Let’s delete candidates who didn’t appear in the Preliminary and Written exams. Therefore, we input 2 3 (Separated by a space).

Entering Input to Run Macro to Delete Row in Excel If the Cell is Blank

Step 8: The Final Output!

➤ Then click on OK.

➤ 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

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

By using the Macro, you can delete the row if the cell is blank from any data set in Excel. Do you have any questions? Feel free to ask us.


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