Ask Your Question

How to define the active cell

asked 2015-02-11 01:10:49 +0200

AvidHunter gravatar image

I have a LibreOffice calc spread sheet with many hidden data input sheets. Whenever I need one of the data input sheets I run the corresponding macro to unhide the sheet. These data input sheets get rather long and having to scroll down to find the next empty row is a bother. I would like to make the unhide macro also find the next available data input row and make the first empty cell in that row the visible active cell. Unfortunately I do not have a clue as to how to go about that. Suggestions would be greatly appreciated.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-02-12 09:58:27 +0200

AvidHunter gravatar image

Thank you Karolus and Pierre-Yves Samyn I've got the macro running using the ctrl + down arrow. Pierre thank you for the write-up as I had totally misunderstood Karolus's suggestions.

This has been a triple win question for me. First because I did not know that that ctrl + down arrow would do that (embarassing though it be). Second; you solved my issue and third because Karolus's second suggestion has giving me a direction to follow with another macro that I am having trouble with, but I will fight with that one for awhile before I draw on everyones expertise again. Thank you

edit flag offensive delete link more

answered 2015-02-11 15:47:34 +0200

pierre-yves samyn gravatar image

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("")

dim args1(0) as new
args1(0).Name = "ToPoint"
args1(0).Value = "$C$3"

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

dim args3(1) as new
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
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.

edit flag offensive delete link more

answered 2015-02-11 01:42:53 +0200

karolus gravatar image

updated 2015-02-11 08:04:00 +0200


Why not shortcut ctrl ↓ ??

doc = thisComponent
controller = doc.CurrentController
sheet = doc.Sheets.getByIndex(0)
curs = sheet.createCursor()
last_used = curs.RangeAddress.StartRow
controller.setActiveSheet( sheet)
edit flag offensive delete link more


I fail to understand this suggestion. Since 'ctrl i' is not a keyboard shortcut that I know of or can find any reference to, are you implying that I should write a new macro to perform the desired function which is run with a keyboard shortcut? I still do not know how to go about writing such a macro.

I pasted your suggestion into my macro and started steping through it to see how it worked. When I got to the first line of the new code I get the error "Object variable not set."

AvidHunter gravatar imageAvidHunter ( 2015-02-11 02:01:59 +0200 )edit

I tried to paste my macro into this window but it would not allow me How did you do that?

AvidHunter gravatar imageAvidHunter ( 2015-02-11 03:03:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-02-11 01:10:49 +0200

Seen: 3,186 times

Last updated: Feb 12 '15