- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Delete Rows with Missing or Blank Values in Certain Columns 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.
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
Then click on Insert, select Module, and copy the below code into the text box.
Insert > Module > Copy.
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
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.
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.
Kickstart Your Career
Get certified by completing the courseGet Started