Move rows with the keyboard in Calc

Question

How might one move a row to a different location with the keyboard? I have found this guide for the mouse but due to disability I have trouble using the mouse for this.

Note that I am not looking to 'sort manually' and using an extra 'sort ordinal' column is not a viable workaround. I am aware of Calc's excellent sorting abilities though.

Solution 1

I'm not sure if there's a way to "move" rows using the keyboard, but using c&p and inserting/deleting rows using the keyboard should offer the same functionality:

• navigate to the first (leftmost) cell of the row to move;
• hit SHIFT+SPACE to select the entire row;
• hit CTRL+C to copy the row;
• hit CTRL+- to delete the current row;
• navigate to the target row;
• hit ALT+I to open the Insert menu;
• hit R to insert a new row (the current row will get shifted downwards);
• hit CTRL+V to paste the row to its new place.

Since cut&paste operations are sometimes quite annoying, you may create a simple macro for cutting cells and another one to paste them, moving existing content down.

Here's a very simple code to "move" selected cells:

Option Explicit

Sub CopyAndCut
    ' ---------------------------------------------------------
    ' define variables
    Dim document   as object
    Dim dispatcher as Object
    Dim oSelections As Object
    ' ---------------------------------------------------------
    ' get access to the document and selections (if any)
    document    = ThisComponent.CurrentController.Frame
    oSelections = ThisComponent.getCurrentSelection()
    If IsNull(oSelections) Then Exit Sub        
    ' ---------------------------------------------------------
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
    dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
    ' -------------------------------------------------------------
    ' Check the width of the selection - if 1024 columns, we assume
    ' the complete row was selected and should get deleted
    If 1024 = oSelections.Columns.getCount() Then
        dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())
    End If
End Sub

Sub InsertWithMoveDown
    ' ---------------------------------------------------------
    ' define variables
    Dim document   as object
    Dim dispatcher as object
    ' ---------------------------------------------------------
    ' get access to the document
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    ' ---------------------------------------------------------
    ' Paste contents with "Move Down" option
    Dim args1(5) as New com.sun.star.beans.PropertyValue
    args1(0).Name = "Flags"
    args1(0).Value = "A"
    args1(1).Name = "FormulaCommand"
    args1(1).Value = 0
    args1(2).Name = "SkipEmptyCells"
    args1(2).Value = false
    args1(3).Name = "Transpose"
    args1(3).Value = false
    args1(4).Name = "AsLink"
    args1(4).Value = false
    args1(5).Name = "MoveMode"
    args1(5).Value = 0
    dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1())
End Sub

Sub InsertWithMoveRight
    ' ---------------------------------------------------------
    ' define variables
    Dim document   as object
    Dim dispatcher as object
    ' ---------------------------------------------------------
    ' get access to the document
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    ' ---------------------------------------------------------
    ' Paste contents with "Move Right" option
    Dim args1(5) as New com.sun.star.beans.PropertyValue
    args1(0).Name = "Flags"
    args1(0).Value = "A"
    args1(1).Name = "FormulaCommand"
    args1(1).Value = 0
    args1(2).Name = "SkipEmptyCells"
    args1(2).Value = false
    args1(3).Name = "Transpose"
    args1(3).Value = false
    args1(4).Name = "AsLink"
    args1(4).Value = false
    args1(5).Name = "MoveMode"
    args1(5).Value = 1
    dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1())
End Sub

After copying the code to your user library, just assign CopyAndCut to, e.g., Alt+C, InsertWithMoveDown to, e.g., Alt+V, and InsertWithMoveRight to, e.g., Alt+R (all these shortcuts are empty by default).

Now, you can select cells or rows using mouse or keyboard, cut them using Alt+C, move to the target cell, and paste them using Alt+V or Alt+R.

Solution 2

To move a row in Open Office Calc:

1. Select the cell in column A of the row to be moved.
2. Hit Shift-Space to highlight the entire row.
3. Press and hold the ALT key.
4. Drag the row (thick black line appears) up or down to the desired location.
5. If your work looks correct, click the Save icon in the toolbar.
6. Otherwise, ALT-Z to undo.

If you want to over-write and destroy the target location, do not hold the ALT key down. Just click on the highlighted row and drag to it's new location. The data at the target location will be destroyed and replaced with the data of the row being moved.

Comments

[Dan Dascalescu]

Moving rows in LibreCalc is super cumbersome. I've raised a bug 3 years ago to implement Excel's simple "Insert cut rows" context menu option, and was told that's a duplicate of a bug raised in 2011: insert copied cells. As of June 2016, this still hasn't been fixed.

[Pavel Tankov]

Works easily here in 2019... Read comments below to see...

[dotancohen]

Terrific, thank you! In LO 4.1 one selects the entire row with Shift-Space. Other than that everything worked fine and I learned two new shortcuts. Thank you!

[Dan Dascalescu]

This gets extra annoying super fast if you have to move more than 1 row.

[Dan Dascalescu]

Try to move 3 rows from row 1 to row 101 (so the current and new location are far enough that drag-and-drop is awkward). You'll find out how annoying it gets when you need to create the empty rows to paste the ones you've copied, then go back to row 1 to delete them (because cutting leaves behind blank rows).

[cyberha4]

Sounds good, but step 4 involves the mouse - while the OP tries to move the rows just using the keyboard.

[cyberha4]

Works with LO Calc, too. Had to swap steps 3 and 4: Start dragging, then press and hold ALT before dropping the rows at their new location.

[cyberha4]

@DanDascalescu: you're right. So, i've written two small macros that should do the annoying part of the work. This way, you can use keyboard shortcuts to cut and paste cells without using the paste special options. I know, it's still far from being a perfect solution...

[Pavel Tankov]

I tested this in Libre Office Calc verison 6.3 and it works exactly as written, without having to reverse 3 and 4. I noticed if you press or release ALT, even while moving the row, you will notice the black lines around the row change... Thick lines (without ALT pressed) to show totally overwriting target row, if ALT is pressed (to move the row), the top line is thick and bottom line is thin. My guess is that will also work in prior versions too, but I could only test in 6.3.

[Pavel Tankov]

You can also work with multiple rows... After pressing SHIFT_SPACE, continue holding down the SHIFT key, then press the UP or DOWN arrow key to select more adjacent rows. Then release the SHIFT key and continue on with the steps as written to move or replace with mouse and ALT key.