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

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.

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

 
 

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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube