Ask Your Question

Select/move to lowest row with data

asked 2017-09-27 18:14:30 +0200

artfulrobot gravatar image

updated 2017-09-27 18:15:38 +0200

Ctrl-* selects the full data set. Great. But what I often, like always seem to need to do is copy something from the top of one column to the bottom of that column in the data set.

Ctrl-Shift ↓ selects to the "lower block margin", which is fine if you're replacing content in your dataset but if you have blank cells it selects down to row 1 million and something. Pasting that much data is a big propblem since (a) it's messy, (b) it expands the data area (Ctrl-*) to having over a million rows, and (c) therefore slows LO to a crawl if the formula you're pasting is non-trivial.

To my mind it is extremely rare to want to select relatively down from one place to the absolute logical limit of the spreadsheet. But if you have a reasonable size data set, say 5,000 rows, selecting down by holding shift and down, or even shift and page down, takes a long time and is frustrating.

Is there a binding so that it would select down to the bottom of the data area? Or accross/up to the side/top of the data area, since the same problem happens if you select to the right.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-09-28 09:25:11 +0200

librebel gravatar image

Hello @artfulrobot,

The following macro moves the cursor to the first empty cell after the last value inside the current column: You could assign your own preferred Shortcut Key to this macro via "Tools : Customize... : Keyboard". Hope it helps, lib

Sub column_MoveAfterLast()
REM Moves the cell cursor to 1 cell below the last used Cell in the current Column.
    Dim oSheet As Object    : oSheet  = ThisComponent.CurrentController.ActiveSheet
    Dim oColumn As Object   : oColumn = oSheet.Columns.getByIndex( ThisComponent.CurrentSelection.RangeAddress.StartColumn )
    Dim oRanges As Object   : oRanges = oColumn.queryContentCells( 1023 )
    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 ) oCell )
End Sub
edit flag offensive delete link more


Thanks for the code! I couldn't get it to work - just seemed to select the cell I was in. I tried to write my own but could not find a reference to all the objects (e.g. I saw that oSheet apparently has a RowDescriptions property which looks like it contains the number of rows, but I could not access that). Anyway, kind of you to offer the code, thanks.

artfulrobot gravatar imageartfulrobot ( 2017-10-04 16:59:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-27 18:14:30 +0200

Seen: 929 times

Last updated: Sep 28 '17