# Excel - deleting row that does not match criteria



## kurk011 (May 11, 2007)

Here's my problem:

I have this data for example:

name score 
anne 1
mark  2
mark 1
test 3
test 3
c 2
c 2
d 10


I want to delete all the rows that i do not need and maintain only the one i need...

so if i want mark and c only.. the data should be

name score
mark 2
mark 1
c 2
c 2



Thanks for all those who will help me figure this one out....


----------



## Nikil (Apr 16, 2007)

Hi..
What exactly you need...You want completely remove row from the sheet or you need a formula that show the resuls at some other place in worksheet.
Are you open for VBA solution?


----------



## Zack Barresse (Jul 25, 2004)

Hello there and welcome to the board!

If you do not want to delete your data then use AutoFilter (found on the Data menu in versions 2003 and prior). However, if you want to actually delete your data, you'll do better with a helper column. Add a header to C1, i.e. "TempHeader". In C2 enter...

=OR(ISNUMBER(FIND("mark",A2)),ISNUMBER(FIND("c",A2)))

Now, the FIND() function is case sensitive. If you do not want to worry about case sensitivity change the FIND() function for the SEARCH() function, which is basically the same thing but functions regardless of case sensitivity. This means you would match "Mark" and "mark" as the same thing. The formula above senses a difference.

Once you have that formula in, select the cell again (C2), place your cursor over the bottom right corner (you should see the cursor turn into the bold *+* sign) and double-click. The formula will autofill down until it meets a blank in the column directly to the left (column B). Then filter column C for FALSE. When you see that, select all of the FALSE cells, press Shift + Spacebar (selects the entire row of the selected cells), right click any selected row header (the left-most row area, left of column A) and click Delete Row. Then unfilter your data (keyboard shortcut Alt + D, F, F).

Hope this helps and good luck!


----------



## kurk011 (May 11, 2007)

VBA solutiuon will do.. since i'll be doing this with large data... imagine a thousand data and i only need like 100 of them...


----------



## Zack Barresse (Jul 25, 2004)

I just gave you a non-VBA solution. I would use VBA as a last resort only. You could look at Ken Puls VBA code to remove duplicates (http://www.excelguru.ca/node/24), the only difference being the formula, which you could use something like I posted above. You can keep adding conditions in the OR() function (up to 30) if you'd like. Although I still recommend you do what I posted.


----------



## Sofia85 (May 11, 2007)

VBA solutiuon will do...sofia:up:


----------



## Zack Barresse (Jul 25, 2004)

Hi Sofia85, welcome to the board!

Did I miss something? How does your URL give a VBA solution? And why would you recommend VBA here? It is definitely *not* needed, nor should it be preferred (unless under certain circumstances, which is what I'm looking forward to hearing).


----------



## kurk011 (May 11, 2007)

Hi Firefytr,

Thanks for the help... but I was trying your solution above and I dont know what I was doing wrong that I cant get it to work


----------



## Zack Barresse (Jul 25, 2004)

Can you post your workbook?


----------



## johnske (Jun 23, 2004)

Zack, I think it's more a matter of kurk wanting to automate the process... 

Kurk, we're assuming several things here
1) the data you want to filter is in column A
2) you have a header
3) column B is empty, if it's not, change the "2" in Columns(2) to another (_unused_) column EDIT: you'll also need to change the "1" in Offset(0,1) to suit {_e.g. if you use Columns(5) then you would need to use Offset(0,4)_}
4) the only criteria you want to keep is "mark" and "c"

In the VBE window paste this code into a standard module and run it from the macro dialog box...

```
Option Explicit

Sub TryThis()
      Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(0, 1).Formula = _
      "=IF(OR(ISNUMBER(FIND(""mark"",A2)),ISNUMBER(FIND(""c"",A2))),TRUE,"""")"
      On Error Resume Next
      With Columns(2)
            .SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Delete
            .ClearContents
      End With
End Sub
```


----------

