In this article, we are going to learn how to extract record with not criteria through VBA in Microsoft Excel.
Let’s take an example to understand how we can put the filter and select the criteria and then take the required action:-
We have data in range A:G columns, in which column A contains Name, column b contains Address, column C contains city, column D region, column E country, column F phone number and column G contains criteria.
We want to write the Macro to put the filter and then filter the data according to Not contains data in G column and then delete the Not contains data. And then remove the filter and show all data.
To extract record with Not criteria follow below given steps:
- Open VBA Page press the key Alt+F11
- Insert a module
- Then go to again insert menu and click on procedure, where you will get the procedure if you don’t know how should be start the macro
- Write the below mentioned code:
Sub Delete_NotEligible() ActiveSheet.Range("$A$1:$G$15").AutoFilter Field:=7, Criteria1:="<>" Rows("2:12").Select Selection.Delete Shift:=xlUp Range("B1").Select Selection.AutoFilter End Sub
- To run the code press key F5 on the keyboard.
- Data will get filtered and then according to set criteria filtered data will be deleted.
- We have define the header range of data to put the filter with criteria of non-blank cells in the eligibility column
- Define in the code to select the row and then will delete the entire row
- At the end open the filter
In this way, we can put the filter on the basis of particular criteria, and then delete the data.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org