Ask Your Question
0

Transpose At Every Blank Row

asked 2018-10-19 22:45:45 +0200

Prasanjith gravatar image

I was wondering if i could transpose data that is in a column at the end every blank cell into new cells in a row. Do i have to use macro???

A1
A2
A3

B4
B5

C6
C7
C8
C9

becomes
A1 | A2 | A3
B4 | B5
C6 | C7 | C8 | C9

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-12-25 22:37:21 +0200

igorlius gravatar image

I guess the original poster is not interested anymore, but for everbody else who is interessted

There is most likely a simpler and more elegant way to do this, but ...

you can use this macro to achive the desired result

Sub LineTranspose

    Set oRange = ThisComponent.CurrentSelection
    oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
    oSheet = ThisComponent.CurrentController.ActiveSheet
    ULC=""  

    dim args(5) as new com.sun.star.beans.PropertyValue
    args(0).Name = "Flags"
    args(0).Value = "SVD"
    args(1).Name = "FormulaCommand"
    args(1).Value = 0
    args(2).Name = "SkipEmptyCells"
    args(2).Value = false
    args(3).Name = "Transpose"
    args(3).Value = true
    args(4).Name = "AsLink"
    args(4).Value = false
    args(5).Name = "MoveMode"
    args(5).Value = 4

    Dim a(oRange.Rows.getCount()) As Integer

    For i = 0 To oRange.Rows.getCount() - 1   
        Set oCell = oRange.getCellByPosition( 0 , i ) 
        if (oCell.String = "")  then
            if (ULC <> "") then 
                oCell = oRange.getCellByPosition( oRange.Columns.getCount() - 1 , i - 1) 
                oConv.Address = oCell.getCellAddress()
                'Print   ULC & ":" &  oConv.PersistentRepresentation 
                TRANGE= ULC & ":" &  oConv.PersistentRepresentation 
                ' Tranpose Range
                cc = ThisComponent.CurrentController
                sheet = cc.ActiveSheet
                frame = cc.Frame
                dh = createUnoService("com.sun.star.frame.DispatchHelper")
                source = sheet.getCellRangeByname(TRANGE)
                cc.select(source)
                dh.executeDispatch(frame, ".uno:Cut", "", 0, Array())
                'Print source.AbsoluteName
                target = sheet.getCellRangeByname(ULC)
                cc.select(target)
                dh.executeDispatch(frame, ".uno:InsertContents", "", 0, args())
                ' clear 
                ULC = ""
            endif
        else
            if (ULC = "") then 
                oConv.Address = oCell.getCellAddress()
                ULC = oConv.PersistentRepresentation    
            endif 
        endif 
    next 


    For i = oRange.Rows.getCount() -1   To 0  Step -1
        Set oCell = oRange.getCellByPosition( 0 , i ) 
        if (oCell.String = "")  then
            oSheet.Rows.removeByIndex(i,1)
        endif
    Next


end Sub

Usage:

Select Data (mind the selected empty cell at the end)

image description

Run the macro on the Selection and you will get:

image description

Maybe it can help someone.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-19 22:45:45 +0200

Seen: 45 times

Last updated: Dec 25 '20