Results 1 to 4 of 4

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

  1. #1
    Join Date
    Nov 2011

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


    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. #2
    Join Date
    Dec 2007

    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:

    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

  3. #3
    Join Date
    Nov 2011

    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. #4
    Join Date
    Aug 2011

    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.

Similar Threads

  1. How to delete first 4 letters of Cell in Microsoft Excel
    By Ulrich in forum MS Office Support
    Replies: 2
    Last Post: 18-01-2012, 05:51 PM
  2. Replies: 6
    Last Post: 23-07-2011, 01:05 AM
  3. Based on Cell Criteria Delete Row in excel
    By AlpAnA$ in forum Windows Software
    Replies: 5
    Last Post: 09-07-2011, 08:50 PM
  4. Replies: 5
    Last Post: 16-10-2010, 02:45 PM
  5. Windows is unable to find a system volume that meets its criteria
    By adrin in forum Vista Setup and Install
    Replies: 2
    Last Post: 05-03-2007, 09:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts