OpenOffice: Delete rows based on cell value

I've got a rather large spreadsheet that I need to filter some rows out of. In the spreadsheet, all rows whose M-Column doesn't equal "on" must be removed. I applied a filter, and re-saved the document, but that kept the unwanted rows - how can I permanently delete these rows instead?

    A    |    B    | ... |  M
-------------------------------
Jonathan | Sampson | ... | on
Jeff     | Atwood  | ... | 
Joel     | Spolsky | ... | on

So in this example, I would want to completely remove Jeff Atwood's record.


Solution 1:

  1. Select a cell in Column M.
  2. Select all cells in the spreadsheet with Ctrl-A.
  3. Open the Data->Filter->Standard Filter menu.
  4. Set the first row of the filter so that Field Name is "Column M", Condition is "Not Equals" and Value is "on".
  5. Click OK to apply the filter to the spreadsheet.
  6. Select all rows in the spreadsheet with Ctrl-A. To unselect any column header cells, hold Ctrl and click the appropriate row.
  7. Right-click on any of the row numbers and select Delete Rows.
  8. Select one of the column header cells, or any non-blank cell.
  9. Use the Data->Filter->Remove Filter menu item to restore the remaining rows.

Solution 2:

My quick and dirty solution would be to create another column with numbers 1..n. Then sort on column M. Select and delete the rows that are not "on". Then restore the original order by sorting on that new column with numbers 1..n.