View source for Documentation/BASIC Guide/Rows and Columns

{{Documentation/BASICGuideTOC/v2
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=Documentation/BASIC Guide/Structure of Spreadsheets
|NextPage=Documentation/BASIC Guide/Cells and Ranges
|spread=block
}}
{{DISPLAYTITLE:Rows and Columns}}
 
Each sheet contains a list of its rows and columns. These are available through the <tt>Rows</tt> and <tt>Columns</tt> properties of the spreadsheet object and support the <idl>com.sun.star.table.TableColumns</idl> and/or <idl>com.sun.star.table.TableRows</idl> services.

The following example creates two objects that reference the first row and the first column of a sheet and stores the references in the <tt>FirstCol</tt> and <tt>FirstRow</tt> object variables.

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim FirstRow As Object
Dim FirstCol As Object

Doc = ThisComponent
Sheet = Doc.Sheets(0)

FirstCol = Sheet.Columns(0)
FirstRow = Sheet.Rows(0)
</source>

The column objects support the <idl>com.sun.star.table.TableColumn</idl> service that has the following properties:

;<tt>Width (long)</tt>:width of a column in hundredths of a millimeter.
;<tt>OptimalWidth (Boolean)</tt>:sets a column to its optimum width.
;<tt>IsVisible (Boolean)</tt>:displays a column.
;<tt>IsStartOfNewPage (Boolean)</tt>:when printing, creates a page break before a column.

The width of a column is only optimized when the <tt>OptimalWidth</tt> property is set to <tt>True</tt>. If the width of an individual cell is changed, the width of the column that contains the cell is not changed. In terms of functionality, <tt>OptimalWidth</tt> is more of a method than a property.

The row objects are based on the <idl>com.sun.star.table.TableRow</idl> service that has the following properties: 

;<tt>Height (long)</tt>:height of the row in 100ths of a millimeter.
;<tt>OptimalHeight (Boolean)</tt>:sets the row to its optimum height.
;<tt>IsVisible (Boolean)</tt>:displays the row.
;<tt>IsStartOfNewPage (Boolean)</tt>:when printing, creates a page break before the row.

If the <tt>OptimalHeight</tt> property of a row is set to the <tt>True</tt>, the row height changes automatically when the height of a cell in the row is changed. Automatic optimization continues until the row is assigned an absolute height through the <tt>Height</tt> property.

The following example activates the automatic height optimization for the first five rows in the sheet and makes the second column invisible.

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim Row As Object
Dim Col As Object
Dim I As Integer

Doc = ThisComponent
Sheet = Doc.Sheets(0)

For I = 0 To 4
   Row = Sheet.Rows(I)
   Row.OptimalHeight = True
Next I

Col = Sheet.Columns(1)
Col.IsVisible = False
</source>

{{Documentation/Note|The <tt>Rows</tt> and <tt>Columns</tt> lists can be accessed through an index in {{OOo}} Basic. The real API call is : <tt>Sheet.getColumns.getByIndex(1)</tt> }}

{{Documentation/VBAnote|Unlike in VBA, the first column has the index 0 and not the index 1.}}

== Inserting and Deleting Rows and Columns ==

The <tt>Rows</tt> and <tt>Columns</tt> objects of a sheet can access existing rows and columns as well as insert and delete them.

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim NewColumn As Object

Doc = ThisComponent
Sheet = Doc.Sheets(0)

Sheet.Columns.insertByIndex(3, 1)
Sheet.Columns.removeByIndex(5, 1)
</source>

This example uses the <tt>insertByIndex</tt> method to insert a new column into the fourth column position in the sheet (index 3 - numbering starts at 0). The second parameter specifies the number of columns to be inserted (in this example: one).

The <tt>removeByIndex</tt> method deletes the sixth column (index 5). Again, the second parameter specifies the number of columns that you want to delete.

The methods for inserting and deleting rows use the <tt>Rows</tt> object function in the same way as the methods shown for editing columns using the <tt>Columns</tt> object.

 
{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Rows and Columns}}
{{PDL1}}