Home » Questions » Computers [ Ask a new question ]

OpenOffice: Delete rows based on cell value

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?

Asked by: Guest | Views: 71
Total answers/comments: 2
Guest [Entry]

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

"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:

Select a cell in Column M.
Select all cells in the spreadsheet with Ctrl-A.
Open the Data->Filter->Standard Filter menu.
Set the first row of the filter so that Field Name is ""Column M"", Condition is ""Not Equals"" and Value is ""on"".
Click OK to apply the filter to the spreadsheet.
Select all rows in the spreadsheet with Ctrl-A. To unselect any column header cells, hold Ctrl and click the appropriate row.
Ctrl-c to copy the selection.
Ctrl-n to open a new, empty spreadsheet.
Ctrl-v to paste the selection.
Ctrl-s to save the new spreadsheet with just the filtered data."