New to Libre - need to delete all rows that contain a cell with a partial string value of “handicap”.
Suggestions?
Thanks
If it is in one (or a few) columns, you can use the filter Data/ More Filters/ Standard Filter and then delete the filtered rows and then Reset Filter.
For many columns it is possible via some macro.
The F&R facility with FindAll can select all the respective cells. A macro can then get all the entire roiws containing such a cell and remove them.
It’s important, however, not to process any row (identified by its index!) a second time, and to correctly regard the automatic movement of the remaining rews.
The following code should do what’s needed.
Sub deleteEntireRowsForCurrentSelection()
REM The current selection may be the result of a single-sheet FindAll by F&R.
doc = ThisComponent
cCtrl = doc.CurrentController
sheet = cCtrl.ActiveSheet
rgs = ThisComponent.CurrentSelection
rowRgs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
For Each rg In rgs
cur = sheet.createCursorByRange(rg)
cur.expandToEntireRows()
rowRgs.addRangeAddress(cur.RangeAddress, True)
Next rg
u = rowRgs.Count - 1
REM The
REM The removal of rows must be done top up (reversely)!
For j = u To 0 Step -1
sheet.removeRange(rowRgs.RangeAddresses(j), 3)
REM The cmode may also be 1 (UP) in place of 3 (ROWS) here.
Next j
End SubAfter F&R the cells are selected, so we can use simply Sheet/ Delete Rows. (or in macro .uno:DeleteRows). Easy solution without the filter :-).
1 Like
Welcome!
What about Standard Filter with Condition Contains, Value handicap and Options Copy results to?
Thanks guys - could not get Standard Filter to work but then duh!
Have to select Show Rows after delete.
Thanks