Clone each row two times

[u4david]

I have spread sheet O.O. 2.3.1 Windows xp pro.

Sheet have 1000+ rows.
Need to clone each row two more times having each row represented identical three times total.

(i will explain my final expectation just in case it can be done in one macro this is what i got:

product1|price1|price2|price3|description product1
Product2|price1|price2|price3|description product2

this is what i need to eventually end up with:

product1|price1|description product1
product1|price2|description product1
product1|price3|description product1
Product2|price1|description product2
Product2|price2|description product2
Product2|price3|description product2

Thank you.

[Villeroy]

Assuming Sheet1.A1:D1001 with first row as column lables and 1000 rows of data.
Get a new sheet
A1:D1 {=$Sheet1.$A$1:$D$1} [array of column labels]
A2:D2 {=OFFSET($Sheet1.$A$1;INT((ROW(A1)-1)/3)+1;0;1;4)}
copy A2:D2
select A3:A3001 and paste (drag&drop will not work in this case)

[turtle47]

Hi u4david,

meets this macro your requirement?

Code: Select all

Sub Insert_2_Rows
dim z
	odoc=thiscomponent
	oSheet = ThisComponent.Sheets().getByIndex(0)'Sheet1
	oCellCursor = oSheet.createCursor()
	oCellCursor.GotoEndOfUsedArea(True)
	Last_Row = oCellCursor.getRangeAddress.EndRow	
	y = Last_Row *3 +1
	myrows=osheet.getrows
	v = 1
	w = 2
	x = 3
	for z = 1 to (y) step 3	
	myrows.insertbyindex(z,2)
	 
	vCell  = ThisComponent.Sheets().getByIndex(0).getCellRangeByName("A" & v & ":E" & v).getDataArray()   
	xCell  = ThisComponent.Sheets().getByIndex(0).getCellRangeByName("A" & w & ":E" & w)
	xCell.setDataArray(vCell())
	yCell  = ThisComponent.Sheets().getByIndex(0).getCellRangeByName("A" & x & ":E" & x)
	yCell.setDataArray(vCell())
	v = v + 3
	w = w + 3
	x = x + 3	
	next
	mycolumns = oSheet.getcolumns
	mycolumns.removebyindex(2,2)
End Sub

Good luck.

JD

[u4david]

I'm not sure how to implement it.I newer used macro.

[Villeroy]

I'm not sure how to implement it.I newer used macro.

But certainly you can use array functions.
=$Sheet1.$A$1:$D$1 finished with Ctrl+Shift+ENter rather than plain Enter gives an array across 4 cells gives:
{=$Sheet1.$A$1:$D$1}

Same input method with ...
=OFFSET($Sheet1.$A$1;INT((ROW(A1)-1)/3)+1;0;1;4)
... in row 2 which then needs to be copied down.