Ask Your Question

Select/move to lowest row with data [closed]

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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-10-16 13:46:24.150268

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

Question Tools

1 follower


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

Seen: 1,138 times

Last updated: Sep 28 '17