Transpose At Every Blank Row

[Prasanjith]

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

[igorlius]

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)

Run the macro on the Selection and you will get:

Maybe it can help someone.