Delete Rows with Missing or Blank Values in Excel

It is critical to have clean and comprehensive data for various data analysis jobs. However, dealing with missing or blank values might be difficult. Fortunately, Excel includes a number of powerful tools and functions that make it simple to discover and delete rows with missing or blank information.

This tutorial will lead you through a step-by-step method for identifying and deleting rows in certain columns of your Excel worksheet that have missing or blank values. Whether you're working with a little dataset or a massive spreadsheet, these strategies can help you clean your data quickly and accurately.

Delete Rows with Missing/Blank Values in Certain Columns

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can delete rows with missing or blank values in certain columns in Excel.

Step 1

Consider an Excel sheet where you have rows with blank values, similar to the below image.

First, right-click on the sheet name and select "View Code" to open the VBA application.

Right click >View Code

Step 2

Then click on Insert, select Module, and copy the below code into the text box.

Insert > Module > Copy.

Example

Sub DeleteRows()
    Dim I As Integer
    Dim xCount As Integer
    Dim xRg As Range
    Dim xTxt As String
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
    Set xRg = Application.InputBox("Please select range:", "Delete Rows Blank", 
xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Areas.Count > 1 Then
        MsgBox "You can't select multiple ranges to operate", vbInformation,
"Kutools for Excel"
        Exit Sub
    End If
    xCount = xRg.Rows.Count
    For I = xCount To 1 Step -1
        If Application.WorksheetFunction.CountBlank(xRg.Rows(I)) > 0 Then
            xRg.Rows(I).EntireRow.Delete
        End If
    Next
End Sub

Step 3

Then click F5 to run the code, select the range of cells, and click Ok. Then you can see that rows with missing values will be deleted.

F5 > Select cells > Ok.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can delete rows with missing or blank values in certain columns in Excel to highlight a particular set of data.