Keyboard shortcut for AutoFill

[Nikos]

I am quite sure, fill down is not the correct term for what I am trying to describe, so I will try to illustrate it. Doubleclicking on the lower left corner of cell B1 will fill cells B2 to B19 with a's.

I would like to know if it is possible to do this without taking my hands from the keyboard.

Regards
Nikos

Edit: Changed subject, was Shortcut for "fill down"  Make your post understandable by others  -- MrProgrammer, forum moderator

[ken johnson]

Ctrl+C to copy B1.
Shift+Down Arrow until B1:B19 is selected.
Ctrl+V.

Ken Johnson

[Nikos]

Thank you for the quick reply. This is actually what I do at the moment.

However, replicating the behavior of the mouse gesture would have the advantage of not loosing the focus from the current cell.
I do realize this is not a very significant problem. But I just can't get rid of the feeling there is a way to replicate the exact behavior.

[acknak]

I agree: that is a handy operation and it would be nice if there was an easier way to use it. I find it difficult to click on that tiny blob, much less double click it.

Unfortunately, it seems that function is not available through the Tools > Customize > Keyboard interface, so there's no way to bind a key to it. Maybe it can be done through a macro--I had no luck with the macro recorder.

There is a function in Tools > Customize > Keyboard, Category: Edit, "Fill Down" but it only works on a selected range; it doesn't automatically detect the fill range like the double-click.

You could bind that to a key, but you'd still have make the selection first--no easier than what you already do.

[Villeroy]

Ctrl+Shift+End [select until end of used range]
Alt+E,i,D [English menu:Edit>Fill>Down]

[Nikos]

Thank you Villeroy...this seems so obvious now.

Acknack, by the way, if you record the macro using the menu entries as described by Villeroy and not by doubleclicking on the lower right border, it works, and you can assign a keyboard shortcut to it.

[Nikos]

It seems that fill down from the menu behaves quite differently compared to the "cross doubleclicking".
It does just copy down (which is useful to me in a lot of cases) but it does not fill down, number series (If B1 is and B2 is 2 the doubleclicking fills B3 to B19 with the numbers 3 to 19 while the macro copies 2 in all cells and alt + EiD is grayed out.

Moreover, if there is something written in a cell to the right of B, I could not use Ctrl + Shift + End for the selection. In other words I can not record the macro. Is there a way to select by keyboard or Macro code the used range in the current column?

[Villeroy]

The column in your screenshot starts with "a" which does not start a sequence anyway.
If it were a1, Alt+E,i,e, Enter auto-fills down series a1,a2,a3, ...
If it were number 1, Alt+E,i,e, Enter auto-fills down series 1,2,3, ...

[Nikos]

That is true. The screenshot is only an example.
However, I don't see the behavior you are describing. when I change b1 to 1, filling down fills column b with 1's. (Hope this is not a 3.4.X version problem)

PS: Please note that I was editing my previous post, while you were answering.

[Nikos]

Lets ignore for the time being this different behaviour. This is the macro I recorded:

Code: Select all

sub Test_Fill_down
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = true

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())


end sub

Is there something I could replace this line:

Code: Select all

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

with, so that it selects the end of data, but only in the same column?
I mean something similar to pressing Shift + End, but selecting the column and not the row.
(I saw that .uno:GoToEndOfRow exists, but .uno:GoToEndOfColumn does not seem to work

Ctrl + Shift + Down does not work because it selects the column to the end of the document and not to the end of the data range.

[Nikos]

I tried about everything I thought could fit from this site
http[size=100]://[/size]www.openshortcu ... on-uno.php
but to no avail.

Edit: 2025-03-10: Broken link, now SPAM -- MrProgrammer, forum moderator

So, I thought, another idea would be to somehow find with .uno:GoToEndOfData the last row in the data range and save it into a variable. Then to tell calc to select the current column up to that row and, then to tell it to fill down.

However, I have not done anything like that before. Going to make some trials on the weekend.

[Villeroy]

Don't fiddle around with dispatch macros unless you like to risk your data.
http://sourceforge.net/projects/ooomacr ... p/download
contains a macro which can fill down/up/left/right all empty ranges within a selection by auto-fill or simple copy.

[Nikos]

Hello Volleroy,

kind of late, but thank you for the link. Quite a view macros in that little file, and I was impressed they are still working even though they obviously stem from another time.
Shows me that most things we are trying to accomplish have been made before.

[Villeroy]

Sheet with some cell content ranges and a push button to trigger a macro dealing with frame, controller and window, used range, current regions, content types, intersections and offsets.

[Punit.Patpatia]

Creating a Macro for a newbie will not be good idea. The best is to create a shortcut. Go to Tools > Customize > Keyboard :: scroll down to Ctrl + F in Shortcut Keys :: Select "Edit" in Category and "Fill Down" in Function and click the button "Modify". This will create a shortcut key CTRL + F to fill down the selected cells.

[svanslyck]

There is a function in Tools > Customize > Keyboard, Category: Edit, "Fill Down" but it only works on a selected range; it doesn't automatically detect the fill range like the double-click.

Thank you - Exactly what this first day user was looking for.