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.
I have created the data below to illustrate the process.
We need to follow the steps below:
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
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.
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.
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.
