

 Thread Tools  Search this Thread 
#1
 
 
excel: delete (or find) cell if it meets certain criteria
Hello, I have a table that scores the number of frames in a movie that fits a certain combination, which is identified by a series of numbers (101010, 10101 for example (71 combinations in total)). From this in the third column I can calculate the fraction of frames that falls into a certain category. What i want to do is to find the fractions that DO NOT fall into a set of combinations. Or delete the ones the fall into a particular set. Basically I want to ignore a bunch of numbers from the first column. Or find a way to highlight where this set of combinations are. Is there a formula for this? My guess is that VLOOKUP would work but i have basic knowledge of it. Hope I managed to explain myself. Thanks in advance. 101011 34 0.165853659 101010 33 0.16097561 10101 29 0.141463415 110101 27 0.131707317 111110 19 0.092682927 11101 14 0.068292683 11111 12 0.058536585 111011 9 0.043902439 111111 7 0.034146341 
#2
 
 
Re: excel: delete (or find) cell if it meets certain criteria
Check the below code that would probably meet up with something to what you want: Code: Sub myDeleteRows() Dim MyCol As String Dim MyVal As Variant Dim i As Integer MyCol = InputBox("column to look through", "Column Search", "A") MyVal = InputBox("Value to look for", "search value", 0) For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1 If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then Range("A" & i).EntireRow.Delete End If Next i End Sub
__________________ Education, Career and Job Discussions 
#3
 
 
Re: excel: delete (or find) cell if it meets certain criteria
Thank for your response. I'm really unfamiliar with macros. Do you think there's a way to do it with a formula. At least is there a formula that searches for multiple items at the same time? 
#4
 
 
Re: excel: delete (or find) cell if it meets certain criteria
It is a kind of filter search and for that you can use Vlookup which can help. I had used this on my excel sheet to find a data from a peculiar row. The follow is like this way : =IF(ISERROR(VLOOKUP(A2, 'Sheet A'!$A$1:$A$14000, 1, FALSE)), "not on Sheet A", "found"). Second this is you can use Autofilter option to hide the data which you do not want to see. 

Tags: excel, formula, lookup 
Thread Tools  Search this Thread 

Similar Threads for: "excel: delete (or find) cell if it meets certain criteria"  
Thread  Thread Starter  Forum  Replies  Last Post 
How to delete first 4 letters of Cell in Microsoft Excel  Ulrich  MS Office Support  2  18012012 05:51 PM 
Looking for a Excel Macro to delete rows which doesn’t contain a value in particular cell  Antariksh  Windows Software  6  23072011 01:05 AM 
Based on Cell Criteria Delete Row in excel  AlpAnA$  Windows Software  5  09072011 08:50 PM 
How can we determine which application meets Java or DotNET eligibility criteria  NathanDS  Software Development  5  16102010 02:45 PM 
Windows is unable to find a system volume that meets its criteria  adrin  Vista Setup and Install  2  05032007 09:27 AM 