Hi,
I am trying to auto-open a spreadsheet and have the last cell in column 1 that has any info in it offset by 1 row down and automatically be made the active cell. So, if a1=2, a2=3, a3=no entry, a4=w, the autopen macro would select a5 in this example.
Thanks
What should happen if A1048576 has content?
I wouldn’t worry much about the speed in this case, but would asume the solution proposed by @llibrebel is very fast. The attempt to get a column from the CurrentSelection will fail, however, if a SheetCellRanges object (regard the plural) is returned. (There even exist multiselections across sheets.)
A SheetCellRange also knows the method queryEmptyCells. We can use it as follows:
Sub goPostLastInLeftmostColumnOfFirstSheet()
theDoc = ThisComponent
theSheet = theDoc.Sheets(0)
eCells = theSheet.Columns(0).QueryEmptyCells
finRg = eCells(eCells.Count - 1)
If finRg.RangeAddress.EndRow<>theSheet.RangeAddress.endRow Then Exit Sub
target = finRg.GetCellByPosition(0, 0)
theDoc.CurrentController.Select(target)
End SubThank you, I believe I flagged your help appropriately
This approach looks even faster, at first glance.
Hello @Dusty50,
To select the (last+1) cell in your column A, you could use the following Basic macro:
( just call it as column_MoveAfterLast(0) ):
Sub column_MoveAfterLast( Optional iColumnIndex )
REM Moves the cell cursor to 1 cell below the last used Cell in the specified Column in the Active Sheet.
REM <iColumnIndex> : Zero-based index of the Column whose cell should be selected; Leave empty for the Current column.
Dim oDoc As Object : oDoc = ThisComponent
If IsMissing( iColumnIndex ) Then iColumnIndex = oDoc.CurrentSelection.RangeAddress.StartColumn
Dim oSheet As Object : oSheet = oDoc.CurrentController.ActiveSheet
Dim oColumn As Object : oColumn = oSheet.Columns.getByIndex( iColumnIndex )
Dim oRanges As Object : oRanges = oColumn.queryContentCells( 1023 ) REM Any content.
Dim lRow As Long
Dim lCount As Long : lCount = oRanges.getCount()
If lCount > 0 Then lRow = oRanges.getByIndex( lCount - 1 ).RangeAddress.EndRow + 1
Dim oCell As Object : oCell = oColumn.getCellByPosition( 0, lRow )
oDoc.CurrentController.select( oCell )
End Sub
With Regards, lib
The column will always be A. It seems to work if a cell in column A is selected before running the macro. If a cell in a different column is selected, it seems to execute the macro on that column.
When you say “just call it as column_MoveAfterLast(0)”, am I supposed to replace line 1 with this?
Finally, when I run this as an auto open macro, I get a runtime error incorrect property value.
A novice here, thanks for your patience
the runtime error is on this line: oColumn = oSheet.Columns.getByIndex( iColumnIndex )
If you report an error, please include the message.
What do you use the term “auto open macro” for in the context?
Auto open is being used when opening document and it appears that your help was perfect
Hello @Dusty50,
No i meant that you could just put that line column_MoveAfterLast(0) inside any method ( such as Main() ), and then execute that method.
@librebel’s answer is straightforward but can be slow in large spreadsheets. If speed is an issue, then use XSearchable instead.
Python code is at Macro VBA excel to calc, adapted from Basic code at Find Last Row with data in a column (View topic) • Apache OpenOffice Community Forum.
i thought that queryContentCells() was pretty fast also, at least compared to traversing the DataArray()…
Might be interesting to find out just how much faster XSearchable would be on large spreadsheets, but i lack the experience to perform such a benchmark test.
That makes sense. I may not have read carefully enough when I wrote the answer.