Tag Archives: OpenOffice

OpenOffice advanced fill down

When people come to me for statistical advise, they often bring Excel files containing data like the following, where values are only written down when they change. But often it is required for further analysis that each row is completely filled with the correct values. There is a basic “fill down” option in OpenOffice (CTRL+D), but up to my knowledge there is unfortunately no extension for this case.

filldown

Years ago I wrote the following lines in Visual Basic for a small company I was visiting that even employed a student assistant for filling out these missing values in enormous automatically generated Excel files (I did this in just a few minutes and of course free of charge, that’s why it is so basic – I just couldn’t see people doing such dreary tasks):

Sub MakroAdvancedFillDown()
  For Each cell In Range("h1:h13215")
    If cell.Value = " " Or cell.Value = "" Then
      cell.Value = old
    End If
    old = cell.Value
  Next cell
End Sub

And given that last week someone with a really large table with the same problem came into my office I took an hour to learn StarOffice Basic and to write the following Macro:

SUB AdvancedFillDown
  oSel = ThisComponent.getCurrentSelection()
  oAdr = oSel.rangeAddress
  oSheet = ThisComponent.CurrentController.ActiveSheet  
  IF NOT oSel.supportsService("com.sun.star.sheet.SheetCellRange") then
      MsgBox "Error: Please select some cells for this makro."
      EXIT SUB
  END IF  
     
  FOR j = oAdr.startColumn TO oAdr.EndColumn
    FOR i = oAdr.startRow TO oAdr.EndRow
      oCell=oSheet.getCellByPosition(j,i)
      IF oCell.string = "" THEN
        oSource = oSheet.getCellRangeByPosition(j,i-1,j,i-1)
        oSourceAdr = oSource.getRangeAddress
        oSheet.copyRange(oCell.getCellAddress,oSourceAdr)
      END IF
    NEXT
  NEXT
END SUB

Now I can deal with this problem with just one click.