How to Extract Record with Not Criteria through VBA

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.

Code Explanation:

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.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

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 info@exceltip.com