Wildcard to delete cells through VBA

While we work with raw data on Excel, we often look for data that match criteria and delete every other data that does not match the given criteria. In this article, we will learn how to get rid of values that do not meet our criteria.

If you come across a situation in which you do not want to delete all the cells instead you want to delete data that does not contain specific text then you should read this article. So without getting into theory let's directly jump to the example.

Example : Delete Every Cell in A range except Cells Containing "ABC"

I have created the data below to illustrate the process.

image 1

 

We need to follow the steps below:

  • Click on the Developer tab
  • From the 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

How does it work?

In this code we are simply clearing the content from the cells that does not match the pattern in the text. So here we have four important lines.

Set criteriarange = Range("A1:B5")

For Each criteriacell In criteriarange

If Not criteriacell.Value Like "*ABC*" Then

criteriacell.ClearContents

End If

The first line is setting the the range in which we want to delete values that do not match our criteria. It is A1:B5.

Now we iterate through each cell in the specified range. To check if the cell contains the text ABC, we use the Like operator, that is used to match patterns in VBA. If the cell contains the text "ABC" the statement returns falls as we have used Not operator and nothing happens. If the cell contains the given pattern, it returns true and the next statement executes that clears the cell content.

The * (astrick) sign is very important. As it is used tell excel that text can have any number of characters in it. You can read about wildcards in excel here.

So yeah guys, this is how you can delete cell content that does not match the given criteria. I hope I was explanatory enough. If you have any doubt regarding this article, I will be happy to answer your queries down below in the comments section below.

 

image 48

Related Articles:

How to Convert Number to Words in Excel in Rupees : We can create a custom Excel formula to convert numbers to words in Indian rupees. I have created this custom function to convert numbers to words in terms of Indian rupees. You can download the macro file

13 Methods of How to Speed Up Excel | Excel is fast enough to calculate 6.6 million formulas in 1 second in Ideal conditions with normal configuration PC. But sometimes we observe excel files doing calculation slower than snails. There are many reasons behind this slower performance. If we can Identify them, we can make our formulas calculate faster.

Center Excel Sheet Horizontally and Vertically on Excel Page : Microsoft Excel allows you to align worksheet on a page, you can change margins, specify custom margins, or center the worksheet horizontally or vertically on the page. Page margins are the blank spaces between the worksheet data and the edges of the printed page

Split a Cell Diagonally in Microsoft Excel 2016 : To split cells diagonally we use the cell formatting and insert a diagonally dividing line into the cell. This separates the cells diagonally visually.

How do I Insert a Check Mark in Excel 2016 : To insert a checkmark in Excel Cell we use the symbols in Excel. Set the fonts to wingdings and use the formula Char(252) to get the symbol of a check mark.

How to disable Scroll Lock in Excel : Arrow keys in excel move your cell up, down, Left & Right. But this feature is only applicable when Scroll Lock in Excel is disabled. Scroll Lock in Excel is used to scroll up, down, left & right your worksheet not the cell. So this article will help you how to check scroll lock status and how to disable it?

What to do If Excel Break Links Not Working : When we work with several excel files and use formula to get the work done, we intentionally or unintentionally create links between different files. Normal formula links can be easily broken by using break links option.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

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

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 Youtube