oCurrentSheet = ThisComponent.Sheets.getByName("current")
right = Ubound(oCurrentSheet.ColumnDescriptions)
bottom = Ubound(oCurrentSheet.RowDescriptions)
oCursor = oCurrentSheet.CreateCursor()
oCursor.gotoEndOfUsedArea(false)
right2 = oCursor.RangeAddress.EndColumn
bottom2 = oCursor.RangeAddress.EndRow
Which of the two is better / more correct ? I am just guessing here, very new to the language.
Hallo
Your »oCurrentSheet« is actually the
…Sheets.getbyName("current")
but IMHO semantically it should:
thisComponent.CurrentController.ActiveSheet
the Value of right is actually a locale-dependent string like Spalte G (locale= german)
which needs additionally processing to become usefull !!… the same for bottom !!
So the semantically and better way is:
doc = ThisComponent
current_sheet = doc.CurrentController.ActiveSheet
cursor = current_sheet.createCursor()
cursor.gotoEndOfUsedArea(false )
'cell_name = cursor.AbsoluteName '→ eg. $Tabelle1.$G$8'
range_address = cursor.RangeAddress
index_lastrow = range_address.EndRow
index_lastColumn = range_address.EndColumn
but if you need simply the whole »used_Range_object«:
doc = ThisComponent
current_sheet = doc.CurrentController.ActiveSheet
cursor = current_sheet.createCursor()
cursor.gotoEndOfUsedArea( True ) 'use True'
'cursor is now the whole used Range'
No it’s actually called current, literally:
But anyways, you are saying the cursor approach is better than ColumnDescriptions / RowDescriptions ? Sounds good, thanks.
First of all I would be glad if the question were more precise about what’s wanted.
XUsedAreaCursor the last cell of the used area needn’t be used itself at all. It’s a cell in the last column containing at least one “used” cell, and there the cell in the last row containing at least one “used” cell again Call the UsedArea the “rectangular closure of all used cells”.Default style is “used”, a cell having a different named cell style of which the settings do not differ from Default is not, nor is a cell being the .Anchor of a graphical object.Well… when I press the End / Ctrl+End in key in libreoffice it usually jumps to what I consider used. Peeking at the documentation says these go to the last column / row which contains data.
The sheet is rather simplistic… imagine it coming from a CSV file, it’s not that far from the truth.
This also will go to the bottommost rightmost cell of the rectangular closure of what you are asking for, and thus to a cell that may not be “used” itself.
As long as we have “literal data” and are assured there’s nothing else (like formulas, graphics, coloring, non-contiguous ranges, empty cells at the margin/end …) that’s ok. As soon as the assurances aren’t explicitly given old inconsistencies may get relevant.
E.g.: The UI action Ctrl+End includes cells of which the column or row is intersecting by area a graphical object. For XUsedAreaCursor such cells aren’t “used” even if the graphic is completely contained in the cell’s area or is anchored to the cell.
For you this may be of no interest (“csv-like”…), but somebody visiting this thread next year hoping to find a solution for his (f/m) problem may not be aware of the ambiguities. They may then use the found “solution” suceessfully for some months, but run into dangerous malfunctions later.
This is a reason for what I would avoid XUsedAreaCursor (and an edited recorded macro as well), and prefer to use the method queryContentCells() from XCellRangesQuery where I know the bug, and can add a respective hint to the answer.
Sub ULTIMA
dim document, dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel" : args1(0).Value = false
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args1())
Dim Var1$, Var2%, Var3%, Var4%
Var1 = ThisComponent.getCurrentSelection().AbsoluteName
MsgBox ThisComponent.getCurrentSelection().AbsoluteName, 0, "CELL ADDRESS"
Var2 = ThisComponent.getCurrentSelection().CellAddress.Sheet
MsgBox ThisComponent.getCurrentSelection().CellAddress.Sheet, 0, "NUMBER SHEET"
Var3 = ThisComponent.getCurrentSelection().CellAddress.Column
MsgBox ThisComponent.getCurrentSelection().CellAddress.Column, 0, "NUMBER COLUMN"
Var4 = ThisComponent.getCurrentSelection().CellAddress.Row
MsgBox ThisComponent.getCurrentSelection().CellAddress.Row, 0, "NUMBER ROW"
End Sub
getUsedRange returns the used range without formatting.
getUsedRange2 returns the used range with formatting.
Function getUsedRange(oSheet)
Dim oCursor1, oCursor2, addr
oCursor1 = oSheet.createCursorByRange(oSheet)
oCursor1.gotoStart()
addr = oCursor1.getRangeAddress()
oCursor2 = oSheet.createCursorByRange(oSheet)
oCursor2.gotoEnd()
addr.EndColumn = oCursor2.RangeAddress.EndColumn
addr.EndRow = oCursor2.RangeAddress.EndRow
getUsedRange = oSheet.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn, addr.EndRow)
End Function
Function getUsedRange2(oSheet)
REM includes formatted blanks
Dim oCursor
oCursor = oSheet.createCursor()
oCursor.gotoStartOfUsedArea(False)
oCursor.gotoEndOfUsedArea(True)
getUsedRange2 = oCursor
End Function