Wildcard to delete cells through VBA

 

VBA to Delete Cells that Does Not Meet Condition in Microsoft Excel: Using For Each Loop

If you came across a situation in which you do not want to delete all the cells instead you want to delete cells that does not contain specific text then you should read this article.

In this article we will learn how to delete cells using wildcard that does not contain the criteria text.

 

Question: I want a macro to help me in deleting the cells in cases wherein the cell contains anything except “ABC” should be deleted.

 

Following is the snapshot of data:

 

image 1

 

We need to follow the below steps:

  • Click on Developer tab
  • From Code group select Visual Basic

 

image 2

 

Enter the following code in the standard module

 

Sub DeleteCells()

Dim criteriarange As Range

Dim criteriacell As Range

Set criteriarange = Range(“A1:B5″)

For Each criteriacell In criteriarange

If Not criteriacell.Value Like “*ABC*” Then

criteriacell.ClearContents

End If

Next criteriacell

End Sub

image 3

 

  • As we run the macro we will get the following output

 

img1

 

Conclusion: In this way using for each loop in VBA we can easily delete cells that does not contain the criteria information.

 

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>