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.
Hallo
Why not shortcut ctrl ↓ ??
doc = thisComponent
controller = doc.CurrentController
sheet = doc.Sheets.getByIndex(0)
curs = sheet.createCursor()
curs.gotoEndOfUsedArea(False)
last_used = curs.RangeAddress.StartRow
controller.setActiveSheet( sheet)
controller.setFirstVisibleRow(last_used)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.”
I tried to paste my macro into this window but it would not allow me How did you do that?
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:
- Click in the first cell of the column containing the data
- Press Ctrl+Down Arrow (expected result: last cell used in the column selected)
- Press Down Arrow (expected result: first empty cell selected)
You can “record” these steps:
- Check Tools> Options> LibreOffice> Advanced> Enable macro recording
- Run Tools> Macros> Record Macro
- Reproduce the sequence above
- Clic “Stop recording” in the Record Macro toolbar
- 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.
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