We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

select "last" cell in column with a macro [closed]

asked 2018-02-07 19:27:43 +0200

Dusty50 gravatar image

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.


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-29 16:51:16.655010


What should happen if A1048576 has content?

Lupp gravatar imageLupp ( 2018-02-08 02:23:45 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2018-02-07 22:06:32 +0200

librebel gravatar image

updated 2018-02-07 22:07:07 +0200

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

image description

edit flag offensive delete link more


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

Dusty50 gravatar imageDusty50 ( 2018-02-08 02:31:57 +0200 )edit

the runtime error is on this line: oColumn = oSheet.Columns.getByIndex( iColumnIndex )

Dusty50 gravatar imageDusty50 ( 2018-02-08 02:36:11 +0200 )edit

If you report an error, please include the message.
What do you use the term "auto open macro" for in the context?

Lupp gravatar imageLupp ( 2018-02-08 02:46:13 +0200 )edit

Auto open is being used when opening document and it appears that your help was perfect

Dusty50 gravatar imageDusty50 ( 2018-02-08 03:07:35 +0200 )edit

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 gravatar imagelibrebel ( 2018-02-08 03:44:14 +0200 )edit

answered 2018-02-08 02:35:21 +0200

Lupp gravatar image

updated 2018-02-08 02:41:00 +0200

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)
 End Sub
edit flag offensive delete link more


Thank you, I believe I flagged your help appropriately

Dusty50 gravatar imageDusty50 ( 2018-02-08 03:06:17 +0200 )edit

This approach looks even faster, at first glance.

librebel gravatar imagelibrebel ( 2018-02-08 03:51:50 +0200 )edit

answered 2018-02-07 22:29:38 +0200

Jim K gravatar image

@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 https://ask.libreoffice.org/en/questi..., adapted from Basic code at https://forum.openoffice.org/en/forum....

edit flag offensive delete link more


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.

librebel gravatar imagelibrebel ( 2018-02-07 23:21:17 +0200 )edit

That makes sense. I may not have read carefully enough when I wrote the answer.

Jim K gravatar imageJim K ( 2018-02-08 19:40:52 +0200 )edit

Question Tools

1 follower


Asked: 2018-02-07 19:27:43 +0200

Seen: 3,404 times

Last updated: Feb 08 '18