[Solved] Delete all blank cells and shift up

[cul]

Is this possible?

[Villeroy]

The following macro selects all blanks within the current selection of cells:

Code: Select all

Sub SelectBlanks()
  view = ThisComponent.getCurrentController()
  x = view.Selection.queryEmptyCells()
  view.select(x)
End Sub

With this selection you can delete rows.

[Lupp]

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

Sub removeEmptyCellRangesFromActiveSheet()      REM Very radical!
doc      = ThisComponent
cCtrl    = doc.CurrentController
sheet    = cCtrl.ActiveSheet
cur      = sheet.createCursor
cur.gotoStartOfUsedArea(False)
cur.gotoEndOfUsedArea(True)
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