Query to delete rows or columns
 

date Done To Be Did
01/03/2008 80 90 Friend
02/03/2008 42 80 friend
02/03/2008 20 70 Boy
04/02/2008 89 90 Friend
05/02/2008 90 90 Boy

In the above, how can i write a simple formula or query to delete a row
whenever Boy is seen in a cell?

Query to delete rows or columns
 

With a macro

Right click the sheet tab, view code and paste this in and run it

Sub stance()
Dim myrange, copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set myrange = Range("D2:D" & Lastrow)
For Each c In myrange
If UCase(c.Value) = "BOY" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
if not copyrange is nothing then
copyrange.Delete
end if

End Sub

Mike

"kilo" wrote:


date Done To Be Did
01/03/2008 80 90 Friend
02/03/2008 42 80 friend
02/03/2008 20 70 Boy
04/02/2008 89 90 Friend
05/02/2008 90 90 Boy

In the above, how can i write a simple formula or query to delete a row
whenever Boy is seen in a cell?

Query to delete rows or columns
 

I'd apply Data|Filter|Autofilter to the Did column.

Then filter to show the rows with Boy in that column.

Then delete the visible rows.

Then remove the filter (data|filter|autofilter) to see all the data again.

======
Another option would be to sort your data by that column so that the Boy's are
all grouped together and then just delete them manually. Since they're in a
single group, it should be pretty quick.

kilo wrote:

date Done To Be Did
01/03/2008 80 90 Friend
02/03/2008 42 80 friend
02/03/2008 20 70 Boy
04/02/2008 89 90 Friend
05/02/2008 90 90 Boy

In the above, how can i write a simple formula or query to delete a row
whenever Boy is seen in a cell?

--

Dave Peterson