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 Not Matching Criteria on Another Sheet
Excel is a powerful data manipulation and analysis application that allows users to edit and analyse data in a variety of ways. When working with huge datasets, it is typical to need to filter and discard entries that do not satisfy specified criteria. While Excel has built-in filtering capabilities, eliminating rows based on criteria from another sheet necessitates a more sophisticated method.
Delete Rows Not Matching Criteria on Another Sheet
Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can delete rows that do not match criteria on another sheet.
Step 1
Consider two Excel sheets where you have a list of items on one sheet and criteria on the other.
First, right-click on the sheet name for the sheet containing items and click View code to open the VBA application.
Right click > View code.
Step 2
Then click on Insert, select Module, and copy the blow code into the text box.
Insert > Module >Copy.
Example
Sub DeleteRow()
Dim rng As Range
Dim Rng1 As Range, Rng2 As Range
Dim arr1 As Variant
Dim arr2 As Variant
Dim dic2 As Variant
Dim OutArr As Variant
xTitleId = "Delete Rows Matching"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Range1 :", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("Range2:", xTitleId, Type:=8)
Set Rng1 = Rng1.Columns(1)
Set Rng2 = Rng2.Columns(1)
Set dic2 = CreateObject("Scripting.Dictionary")
arr1 = Rng1.Value
arr2 = Rng2.Value
For i = 1 To UBound(arr2, 1)
xKey = arr2(i, 1)
dic2(xKey) = ""
Next
Rng1.ClearContents
OutArr = Rng1.Value
xIndex = 1
For i = 1 To UBound(arr1, 1)
xKey = arr1(i, 1)
If dic2.Exists(xKey) Then
OutArr(xIndex, 1) = xKey
xIndex = xIndex + 1
End If
Next
Rng1.Value = OutArr
End Sub
Step 3
Then click F5 to run the module, select the range of items, and click Ok.
F5 > Select range > Ok.
Step 4
Then select the cells containing criteria and click OK to complete the task.
Select cells > Ok.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can delete rows that do not match criteria on another sheet to highlight a particular set of data.
113 Views
