Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hi - Karolus's first proposal was to use the shortcut Ctrl+Down Arrow that moves the cursor to the bottom edge of the current data range.

The idea is to reproduce by macro these steps:

  1. Click in the first cell of the column containing the data
  2. Press Ctrl+Down Arrow (expected result: last cell used in the column selected)
  3. Press Down Arrow (expected result: first empty cell selected)

You can "record" these steps:

  1. Check Tools> Options> LibreOffice> Advanced> Enable macro recording
  2. Run Tools> Macros> Record Macro
  3. Reproduce the sequence above
  4. Clic "Stop recording" in the Record Macro toolbar
  5. Give a name and save your macro in the document

For example, if the desired column is column C the recorded code will look like this:

sub FirstEmpty

dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args3())


dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args5())

end sub

Karolus's second proposal (listing) uses the methods of the API to find the last row used in the sheet (which is not necessarily that of the column on which you want to work). This is a good method but it needs to be adapted to your spreadsheet and requires knowledge of the language.