OO-Snippets: split column

Commons

Keywordscolumn, split
LanguageOOBasic
ApplicationCalc
AuthorsSasa Kelecevic (initial)
Supported Versions
Supported OS
Question How to split column

How to split column into the form of n columns by m rows.

Answer

The following macro split column A in Sheet1 into 5 columns.

First select a cell in new sheet ie. Sheet2 and then run macro.

Code-Snippet-Listing (snippet-source)


REM ***** BASIC *****


Sub Main

SplitColumn ( "Sheet1","A",5)

End Sub


Sub SplitColumn ( SheetName As String , ColumnName As String , ColumnsNo As Integer )

iSheetIndex = ThisComponent.CurrentSelection.CellAddress.Sheet

oSheet = ThisComponent.Sheets.getByIndex( iSheetIndex )

RowsNo = GetLastUsedRow( ThisComponent.Sheets.getByName ( SheetName ) )

' status bar progress indikator

oBar = THISCOMPONENT.CurrentController.StatusIndicator

iNum = 1

oBar. start ( "Split column " & ColumnName & " in sheet " & SheetName, RowsNo/ColumnsNo )

For nR = 0 to RowsNo/ColumnsNo

oBar.setValue ( nR )

For nC = 0 To ColumnsNo - 1

oSheet.getCellByPosition (nC,nR).setFormula("=" & SheetName & "." & ColumnName & iNum )

iNum = iNum + 1

Next nC

Next nR


oBar.End


End Sub


' from Tools library

Function GetLastUsedRow(oSheet as Object) as Integer

Dim oCell As Object

Dim oCursor As Object

Dim aAddress As Variant

oCell = oSheet.GetCellbyPosition(0, 0)

oCursor = oSheet.createCursorByRange(oCell)

oCursor.GotoEndOfUsedArea(True)

aAddress = oCursor.RangeAddress

GetLastUsedRow = aAddress.EndRow

End Function

Changelog

DateUserModification

and