Swap two rows with a macro function

[capdr]

I would like to swap two rows with a macro. I already did that using cut and paste. I want a better way. Thanks in advance

[Villeroy]

ALT+Drag&Drop

[capdr]

How can I convert this into a macro?

[Villeroy]

Just write a macro.

[capdr]

sub SwapRows
	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")
	
	Print "Are you sure?"
	
	rem ----------------------------------------------------------------------
	dim args1(0) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "ToPoint"
	args1(0).Value = "A1:B1"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
	
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
	
	rem ----------------------------------------------------------------------
	dim args3(0) as new com.sun.star.beans.PropertyValue
	args3(0).Name = "ToPoint"
	args3(0).Value = "D1"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
	
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
	
	rem ----------------------------------------------------------------------
	dim args5(0) as new com.sun.star.beans.PropertyValue
	args5(0).Name = "ToPoint"
	args5(0).Value = "A" & pointer & ":B" & pointer
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
	
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
	
	rem ----------------------------------------------------------------------
	dim args7(0) as new com.sun.star.beans.PropertyValue
	args7(0).Name = "ToPoint"
	args7(0).Value = "A1"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())
	
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
	
	rem ----------------------------------------------------------------------
	dim args9(0) as new com.sun.star.beans.PropertyValue
	args9(0).Name = "ToPoint"
	args9(0).Value = "D1:E1"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())
	
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
	
	rem ----------------------------------------------------------------------
	dim args11(0) as new com.sun.star.beans.PropertyValue
	args11(0).Name = "ToPoint"
	args11(0).Value = "A" & pointer & ":B" & pointer
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())
	
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
	
	rem ----------------------------------------------------------------------
	dim args17(0) as new com.sun.star.beans.PropertyValue
	args17(0).Name = "ToPoint"
	args17(0).Value = "A1"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args17())
	
	pointer = pointer + 1
end sub

I have this but I want to improve it. Can you help me? Thanks

[sokol92]

Try this.

' Swap sheet rows.
' - oSheet spreadsheet.
' - r1     row 1 index. 
' - r2     row 2 index.
'
' Returns: True / False.
Function SwapRows(ByVal oSheet, ByVal r1 As Long, r2 As Long) As Boolean
  Dim adrR, adrC, n As Long
  If r2<r1 Then ' swap r1 and r2
    n=r2 : r2=r1 : r1=n
  End If  
  
  With oSheet
    adrR=.RangeAddress
    If r1=r2 Or r1<0 Or r2>adrR.EndRow Then Exit Function
    .Rows.insertByIndex(r2, 1)
    adrR.StartRow=r1
    adrR.EndRow=r1
   .CopyRange(.getCellByPosition(0, r2).CellAddress, adrR)
    adrR.StartRow=r2+1
    adrR.EndRow=r2+1
   .CopyRange(.getCellByPosition(0, r1).CellAddress, adrR)   
   .Rows.removeByIndex(r2+1, 1)
  End With 
  SwapRows=True
End Function

Sub TestSwapRows
  ' swap rows 1 and 3 of active sheet
  SwapRows(ThisComponent.CurrentSelection.SpreadSheet, 1, 3)
End Sub 

[eeigor]

There is row 1 and row 2. We need to insert an empty row before row 1, copy the data of row 2 into it, and remove row 2. Done.
Link 1
Link 2

Insert: sheet.Rows.insertByIndex(start, rows)
Copy: Try row_range.DataArray property (get, set).
Remove: sheet.Rows.removeByIndex(start, rows)