OpenOffice: Delete rows based on cell value

Question

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.

Answer 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.

Answer 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.

Answer 3

The accepted answer doesn't work for groups of non-consecutive rows to be deleted. Specifically, selecting all rows using ctrl-a includes the hidden rows in selection, so hidden steps would be deleted in step 7 as well.

I sometimes use the reordering tip from another answer.

My workaround, though would be to do steps 1-6 from the accepted answer, then ctrl-c, ctrl-n, ctrl-v, ctrl-s to save the selection in a new spreadsheet. That may lose some formatting like column widths, though.

The whole procedure:

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. Ctrl-c to copy the selection.
8. Ctrl-n to open a new, empty spreadsheet.
9. Ctrl-v to paste the selection.
10. Ctrl-s to save the new spreadsheet with just the filtered data.

Answer 4

When you apply a filter, you only show/hide the desired rows. Try this: FIRST - HAVE A BACKUP...hehehe After apply a filter, you need select the rows, right button of the mouse and select: Exclude, Remove, Delete (I dont know which option will show.