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.

image 1


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:="<>"


Selection.Delete Shift:=xlUp



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.

image 2


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.


image 48

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


Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>