Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)

While working with VBA in Excel, we often need to delete a row if any cell is blank. In this article, I’ll show you how you can delete a row if a cell is blank through step-by-step analysis with proper illustrations.

Excel VBA: Delete Row If Cell Is Blank (Quick View)

Sub Delete_Rows_with_Blank_Cells_in_Single_Column()

Worksheets("Sheet1").Activate

Set Rng = ActiveSheet.UsedRange

Blank_Cells_Column = 4

For i = Rng.Rows.Count To 1 Step -1
    If Rng.Cells(i, Blank_Cells_Column) = "" Then
        Rng.Cells(i, Blank_Cells_Column).EntireRow.Delete
    End If
Next i

End Sub

How to Delete Row If Cell Is Blank with Excel VBA (Step-by-Step Analysis)

Without further delay, let’s move to our main discussion. Here we’ve got a data set with the Names of some students and their Marks in Physics, Chemistry, and Biology in a school.

Some cells within the marks are blank cells. This means the student didn’t appear in that particular exam.

Our objective today is to remove the rows that contain blank cells from this data set with the help of Excel VBA.

Read More: How to Use VBA to Delete Empty Rows in Excel

1. Run a VBA Macro to Delete a Row If the Cell Is Blank in a Single Column with Excel VBA

First of all, we’ll develop a Macro that’ll delete rows with blank cells from a single column. I am showing you the step-by-step procedure to accomplish this.

⧪ Step 1: Activating the Source Worksheet

The 1st step that we need to accomplish is to activate the source worksheet. Here the source worksheet is Sheet1. The line of code will be:

Worksheets("Sheet1").Activate

This step isn’t mandatory if the source worksheet is already active before running the code.

⧪ Step 2: Setting the Desired Range on the Active Worksheet

Then we have to set the range from which we’ll delete the rows with blank cells. To capture all the used cells of the worksheet, you can use the UsedRange property of VBA.

Set Rng = ActiveSheet.UsedRange

⧪ Step 3: Identifying the Column with the Blank Cells

Next, we have to identify the column from which the blank cells will be removed. For the sake of this example, we want to remove the students who didn’t appear in the Physics exam.

That is, we want to remove the rows with the blank cells in column 2.

The line of code will be:

Blank_Cells_Column = 4

⧪ Step 4: Iterating through a Backward Loop to Remove the Rows with Blank Cells

Finally, we have to iterate through a for-loop to remove the rows with the blank cells. The iterations will be backward.

The lines of code will be:

For i = Rng.Rows.Count To 1 Step -1
    If Rng.Cells(i, Blank_Cells_Column) = "" Then
        Rng.Cells(i, Blank_Cells_Column).EntireRow.Delete
    End If
Next i

So the complete VBA code will be:

⧭ VBA Code:

Sub Delete_Rows_with_Blank_Cells_in_Single_Column()

Worksheets("Sheet1").Activate

Set Rng = ActiveSheet.UsedRange

Blank_Cells_Column = 4

For i = Rng.Rows.Count To 1 Step -1
    If Rng.Cells(i, Blank_Cells_Column) = "" Then
        Rng.Cells(i, Blank_Cells_Column).EntireRow.Delete
    End If
Next i

End Sub

⧭ Output:

Run the code. It’ll remove the rows with the blank from the column called Marks in Physics.

2. Create a Macro to Delete a Row If the Cell Is Blank in Multiple Columns with Excel VBA

This time we’ll learn to remove rows with blank cells from multiple columns. Let’s try to remove the rows with blank cells in Chemistry and Biology.

The steps are the same as Method 1. Just in Step 1, we’ll declare an array of columns instead of just one column from where the blank cells will be removed. Here it’s columns 3 and 4.

Dim Blank_Cells_Columns As Variant
Blank_Cells_Columns = Array(3, 4)

And in Step 4, we’ll iterate through two for-loops instead of one.

For i = Rng.Rows.Count To 1 Step -1
    For j = LBound(Blank_Cells_Columns) To UBound(Blank_Cells_Columns)
        If Rng.Cells(i, Blank_Cells_Columns(j)) = "" Then
            Rng.Cells(i, Blank_Cells_Columns(j)).EntireRow.Delete
            Exit For
        End If
    Next j
Next i

So the complete VBA code will be:

⧭ VBA Code:

Sub Delete_Rows_with_Blank_Cells_in_Multiple_Columns()

Worksheets("Sheet1").Activate

Set Rng = ActiveSheet.UsedRange

Dim Blank_Cells_Columns As Variant

Blank_Cells_Columns = Array(3, 4)

For i = Rng.Rows.Count To 1 Step -1
    For j = LBound(Blank_Cells_Columns) To UBound(Blank_Cells_Columns)
        If Rng.Cells(i, Blank_Cells_Columns(j)) = "" Then
            Rng.Cells(i, Blank_Cells_Columns(j)).EntireRow.Delete
            Exit For
        End If
    Next j
Next i

End Sub

⧭ Output:

Run the code. This time it’ll remove the students who didn’t appear in either Chemistry or Biology exam.

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel

3. Use a UserForm to Delete Row If Cell Is Blank with Excel VBA

Finally, we’ll develop a UserForm to delete a row if any cell is blank with Excel VBA.

⧪ Step 1: Inserting a New UserForm

First of all, go to the Insert > UserForm button of the Visual Basic Editor to insert a new UserForm.

⧪ Step 2: Dragging the Necessary Tools

A UserForm called UserForm1 will be created with a Toolbox called Control. Drag 2 Labels, 2 ListBoxes, and a CommandButton to the UserForm.

Change the displays of the Labels to Worksheet: and Delete Empty Rows from. Similarly, change the display of the CommandButton to OK.

⧪ Step 3: Inserting Code for ListBox1

Double click on ListBox1. A Private Subprocedure called ListBox1_Click will open. Insert the following code there.

Private Sub ListBox1_Click()

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Worksheets(UserForm1.ListBox1.List(i)).Activate
        ActiveSheet.UsedRange.Select
        UserForm1.ListBox2.Clear
        For j = 1 To ActiveSheet.UsedRange.Columns.Count
            UserForm1.ListBox2.AddItem ActiveSheet.UsedRange.Cells(1, j)
        Next j
        Exit For
    End If
Next i

End Sub

⧪ Step 4: Inserting Code for CommandButton1

Add this code for the CommandButton1 (CommandButton1_Click).

Private Sub CommandButton1_Click()

Set Rng = ActiveSheet.UsedRange

Dim Blank_Cells_Columns() As Variant
ReDim Blank_Cells_Columns(0)
Blank_Cells_Columns(0) = "ExcelDemy"

Count = 1

For i = 0 To UserForm1.ListBox2.ListCount - 1
    If UserForm1.ListBox2.Selected(i) = True Then
        ReDim Preserve Blank_Cells_Columns(Count)
        Blank_Cells_Columns(Count) = i + 1
        Count = Count + 1
    End If
Next i

For i = Rng.Rows.Count To 1 Step -1
    For j = LBound(Blank_Cells_Columns) + 1 To UBound(Blank_Cells_Columns)
        If Rng.Cells(i, Blank_Cells_Columns(j)) = "" Then
            Rng.Cells(i, Blank_Cells_Columns(j)).EntireRow.Delete
            Exit For
        End If
    Next j
Next i

Unload UserForm1

End Sub

⧪ Step 5: Inserting Code for Running the UserForm

Finally, insert a new Module and add this code for running the UserForm.

Sub Run_UserForm()

UserForm1.Caption = "Delete Rows with Blank Cells"

UserForm1.ListBox1.ListStyle = fmListStyleOption

UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox2.MultiSelect = fmMultiSelectMulti

For i = 1 To Sheets.Count
    UserForm1.ListBox1.AddItem Sheets(i).Name
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
        UserForm1.ListBox1.Selected(i) = True
    End If
Next i

Load UserForm1
UserForm1.Show

End Sub

⧪ Step 6: Running the UserForm (The Final Output)

Your UserForm is now ready to use. Run the Macro called Run_UserForm.

The UserForm will load in the worksheet with the caption Delete Rows with Blank Cells. Select your desired worksheet and columns from the UserForm.

Here I’ve selected Sheet1 and Marks in all three subjects, Physics, Chemistry, and Biology.

Then click on the button OK. The students absent from any of the 3 exams in Sheet1 will be removed automatically.

Read More: How to Apply VBA Code to Delete Rows Based on Multiple Cell Value

Things to Remember

In all the codes of this article, I’ve used the UsedRange property of VBA to cover all the used cells of the worksheet. If you want to capture a specific region, set the address of that specific range instead.

Download Practice Workbook

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

Conclusion

So this is these are the ways to delete a row if any cell is blank with VBA in Excel. Hope the examples made everything pretty clear for you. Still, if you have any questions, feel free to ask us. Don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• How to Filter and Delete Rows with VBA in Excel
• How to Delete Hidden Rows in Excel VBA
• VBA to Delete Every Other Row in Excel
• Excel VBA to Delete Row If Cell Contains Partial Text
• How to Apply VBA to Delete Rows with Specific Data in Excel
• How to Delete Row Using Macro If Cell Contains 0 in Excel