Reverse columns order in libre office calc

4
votes

Suppose I have This This Cells:

Initial Enteries

And then for some reason I want to reverse the order of columns automatically to become something like This:

enter image description here

any help will be appreciated!

3
libreoffice-calc
How is this question related to programming? Insert a new empty column A left before old column A. Cut/Paste the now last column D therein. Now Cut/Paste the column B to the now empty column D. Delete the now empty column B. – Axel Richter
@AxelRichter Thank you for your comment. But I think there is a misunderstanding. This 3 columns are just an example. I want to do this for large number of columns. So Cut and Paste is not What I looking for. This should probably via programming. – SirSaleh
So what programming language shall be used? – Axel Richter
Any script that can use in libre-office calc. Sorry I don't know what programming tools Libre-office calc has in itself! – SirSaleh

3 Answers

7
votes

quick steps:

  • insert new row above 1
  • fill row with monoton increasing integer index to the right.
  • Then select your data and sort descending, so highest indexed right col is first
  • copy and paste the result


https://gr-plus.blogspot.com/2015/01/reverse-column-or-row-order-in-excel-or.html

1
votes

So here is an approach using Libreoffice Basic and the Libreoffice API.

sub ReverseColumns()

 oThisWorkbook = ThisComponent
 oActiveSheet = oThisWorkbook.CurrentController.ActiveSheet

 oRow1 = oActiveSheet.getRows().getByIndex(0)
 aFilledCellsRow1 = oRow1.queryContentCells(1+2+4+16).getRangeAddresses()

 if ubound(aFilledCellsRow1) = -1 then exit sub

 lLastFilledColumnRow1 = aFilledCellsRow1(ubound(aFilledCellsRow1)).EndColumn

 c = 0
 for i = lLastFilledColumnRow1 to 1 step -1
  oCellTargetColumn = oActiveSheet.getCellByPosition(c, 0)
  oRangeAddressTargetColumn = oCellTargetColumn.RangeAddress
  oActiveSheet.insertCells(oRangeAddressTargetColumn, com.sun.star.sheet.CellInsertMode.COLUMNS)

  oCellTargetColumn = oActiveSheet.getCellByPosition(c, 0)
  oCellAddressTargetColumn = oCellTargetColumn.CellAddress

  oRangeSource = oActiveSheet.Columns.getByIndex(lLastFilledColumnRow1 + 1)
  oRangeAddressSource = oRangeSource.RangeAddress
  oActiveSheet.moveRange(oCellAddressTargetColumn, oRangeAddressSource)
  c = c + 1
 next

end sub

This first determines the last filled column in row 1. The column reversing process will then be done until that column.

For learning about Macros in Libreoffice start here: https://wiki.documentfoundation.org/Macros

0
votes

In case anyone here wants to reverse the order of rows (instead of columns) ... I took the macro code posted by @Axel-Richter and edited it so it does just that:

sub ReverseRows()

  oThisWorkbook = ThisComponent
  oActiveSheet = oThisWorkbook.CurrentController.ActiveSheet

  oColumn1 = oActiveSheet.getColumns().getByIndex(0)
  aFilledCellsColumn1 = oColumn1.queryContentCells(1+2+4+16).getRangeAddresses()

  if ubound(aFilledCellsColumn1) = -1 then exit sub

  lLastFilledRowColumn1 = aFilledCellsColumn1(ubound(aFilledCellsColumn1)).EndRow

  c = 0

  for i = lLastFilledRowColumn1 to 1 step -1
    oCellTargetRow = oActiveSheet.getCellByPosition(0, c)
    oRangeAddressTargetRow = oCellTargetRow.RangeAddress
    oActiveSheet.insertCells(oRangeAddressTargetRow, com.sun.star.sheet.CellInsertMode.ROWS)

    oCellTargetRow = oActiveSheet.getCellByPosition(0, c)
    oCellAddressTargetRow = oCellTargetRow.CellAddress

    oRangeSource = oActiveSheet.Rows.getByIndex(lLastFilledRowColumn1 + 1)
    oRangeAddressSource = oRangeSource.RangeAddress
    oActiveSheet.moveRange(oCellAddressTargetRow, oRangeAddressSource)
    c = c + 1
  next

end sub