[Solved] Delete all blank cells and shift up

Discuss the spreadsheet application

[Solved] Delete all blank cells and shift up

Postby cul » Thu Jan 23, 2020 10:16 am

Is this possible?
Last edited by cul on Thu May 21, 2020 7:48 am, edited 1 time in total.
LibreOffice on Windows 7 (sometimes Mac)
Posts: 51
Joined: Mon Jan 29, 2018 1:15 pm

Re: Delete all blank cells and shift up

Postby Villeroy » Thu Jan 23, 2020 3:03 pm

The following macro selects all blanks within the current selection of cells:
Code: Select all   Expand viewCollapse view
Sub SelectBlanks()
  view = ThisComponent.getCurrentController()
  x = view.Selection.queryEmptyCells()
End Sub

With this selection you can delete rows.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete all blank cells and shift up

Postby Lupp » Fri Jan 24, 2020 1:03 am

The multiselection allows to clear its cells from contents, but not to remove the cells from the sheet (filling columns up from the bottom). [Removal of complete rows or columns is supported.]
What the questioner asked for would be done by:
Code: Select all   Expand viewCollapse view
Sub removeEmptyCellRangesFromActiveSheet()      REM Very radical!
doc      = ThisComponent
cCtrl    = doc.CurrentController
sheet    = cCtrl.ActiveSheet
cur      = sheet.createCursor
curCols  = cur.Columns
For Each col In curCols
  eRgs     = col.queryEmptyCells
  u        = eRgs.Count-1                       REM Reverse order necessary!
  For j = u To 0 Step - 1                       REM Otherwise a new query needed before the next step!
    sheet.removeRange(eRgs.RangeAddresses(j), 1)REM com.sun.star.sheet.CellDeleteMode.UP = 1
  Next j
Next col
End Sub
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 2910
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 21 guests