How to swap two cells in LibreOffice Calc

Question

I'd like to be able to select two cells in LibreOffice Calc, and press a magic key of some kind, with the result that the content of the two cells are exchanged.

How do I do that?

I found this solution for Excel, but it doesn't work in LibreOffice: https://superuser.com/questions/257516/swap-cell-contents-in-excel

Thanks Jesper.

Click on the column, click and hold a left mouse button on highlighted area, start dragging, click Alt button before release the mouse button — Serhii Shliakhov, Nov 13 '18 at 10:44

score 5 · Answer 1 · answered Apr 25 '17 at 16:05

5

I realize this question was posted some time ago, but now, as of at least LibreOffice 4.2, you can achieve this via Alt+drag. I was looking for this solution today, and after seeing @wolfQueen's answer, I tried random key combos until I discovered this.

Depending on your system, this may be a bound action. In my OS and desktop environment (Ubuntu 16.04, XFCE) Alt+drag is a window move action, so I have to start dragging the cell, then press Alt to prevent the window manager from stealing the show.

Hope this helps anyone with this same question!

answered Apr 25 '17 at 16:05

A.Ford

1,024
8
10

It simply copy and paste while shift the cells down. It doesn't swap cells. – Naveed Abbas Nov 06 '19 at 10:22
Doesn't swap in LO Calc 7.4; works as noted above by @Naveed Abbas. – David Yockey Jul 18 '23 at 13:37

score 1 · Answer 2 · answered Nov 09 '19 at 07:13

There are two macros you can use to swap the cells

Credit: Mauricio

Sub swap_values()

doc = ThisComponent
sel = doc.CurrentSelection

If sel(0).ImplementationName = "ScCellObj" Then
    c1 = sel(0)
    c2 = sel(1)
Else
    If sel(0).Columns.Count = 2 Then
        c1 = sel(0).getCellByPosition(0,0)
        c2 = sel(0).getCellByPosition(1,0)
    Else
        c1 = sel(0).getCellByPosition(0,0)
        c2 = sel(0).getCellByPosition(0,1)      
    End If
End If

tmp = c1.String
c1.String = c2.String
c2.String = tmp

End Sub

There's another macro I wrote, it uses a different approach.

Credit Navshah

Sub Swap()

Dim selection as Object
selection = ThisComponent.getCurrentSelection()

cell1Address = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
cell2Address = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")

cell1Address.Address = selection(0).getRangeAddress 
cell2Address.Address = selection(1).getRangeAddress

cell1 = cell1Address.UserInterfaceRepresentation
cell2 = cell2Address.UserInterfaceRepresentation

REM if the cells are in contagious single range
If selection.supportsService("com.sun.star.sheet.SheetCellRange") Then

    arr = Split(cell1, ":")
    cell1 = arr(0)
    cell2 = arr(1)

End If


REM // Get the cell references to write values

cellA = ThisComponent.Sheets(0).getCellRangebyName(cell1)
cellB = ThisComponent.Sheets(0).getCellRangebyName(cell2)

REM // Store CellA values temporarily in a string variable 't'

Dim t as String
t = cellA.String

cellA.String = cellB.String
cellB.String = t



End Sub

Save any of the above macros and assign a shortcut to it.

The complete post can be found here

Unfortunately, neither macro is able to swap contents of cells formatted as currency while retaining the format. Only the text of the cell contents is swapped while the formatting is lost. — David Yockey, Jul 18 '23 at 14:21

score 0 · Answer 3 · edited May 23 '17 at 11:54

0

This is maybe not a simple solution, but you could write a macro to read the values of the selected cells and switch them. Then you can link the macro either to a pushbutton on your sheet or to a button you add in the LibreOffice toolbar if you want to reuse it for several documents.

This answer could help you do that: LibreOffice Calc: Access a user selected range in a macro

edited May 23 '17 at 11:54

Community

1
1

answered Oct 01 '14 at 08:05

SpacemanSpiff

249
7
17

score 0 · Answer 4 · answered Oct 29 '15 at 13:59

0

I believe the answer to be incorrect. You cannot do this from a macro if you call the macro from the same sheet as a function since a function cannot change any cell from the calling sheet.

answered Oct 29 '15 at 13:59

Andrew

816
7
15

score 0 · Answer 5 · answered Apr 26 '16 at 03:24

I failed to find the "magic key" too.

But as a work around fix, what I've done is I selected the row by clicking it's row number at the leftmost part, then press Alt + Ctrl, then move/drag it to the top or to the bottom of the row where you want to exchange it. It did just copy the row actually, but then you can remove/delete the row from its original position. And it will end up like you exchange it.

Hope it helps, just comment for any questions or suggesstions

How to swap two cells in LibreOffice Calc

5 Answers5