Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
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.
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.
203 Views
