excel: delete (or find) cell if it meets certain criteria

[cmendes]

Sponsored Links

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

[EINSTEIN_007]

Check the below code that would probably meet up with something to what you want:

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

[cmendes]

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?

[AnitG]

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.