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.
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):
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:
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.