Keypress (or macro) to jump to last non-empty cell

[joshua4]

I want to be able to jump (to the right, really) to the last cell whose formula results in a non-empty value. So imagine ctrl+shift+right but where the jump goes to the last cell that appears empty even if later cells have formulas. In other words, I have a long row of cells each with a given formula, and I want to be able to jump to the last cell in that row where the formula actually produces a visible result, i.e., <>"".

I thought I’d ask before creating a macro, since it seems like it would be a pretty handy thing people might have lying around; horizontal scrolling is always a pain, but so many spreadsheets traditionally build to the right.

[Villeroy]

Ctrl+End

[erAck]

That’s not what is wanted (apart from that it jumps to the bottom right and not just to the outer right), it includes any cell with content, also formulas that return empty strings.
There’s no such shortcut, a macro can jump to the right data edge but then has to compare results going left until not empty.

[Villeroy]

https://extensions.libreoffice.org/en/extensions/show/1983

[joshua4]

Thanks. I confess to not thinking about extensions. I’m sure it would fit the bill, but instead of using it as a whole I think I’ll look at the Python, and see what they are doing for the text selection part. That may or may not save me time, but it will get me looking at Python and extension code.

[joshua4]

Yep. Looks like I can call queryFormulaCells(1) [1=NUMERIC] on the current row and it’s pretty much done. In my case here any non-empty result will ultimately refer to a date, which is seen as numeric by queryFormulaCells, while “” is not. So then I can just pull the EndColumn from that returned XSheetCellRanges interface’s RangeAddresses struct and set the selection. I think it did go quicker to see similar things in use in the extension by Andreas Säger. I’ll edit this comment with the macro once I get it buttoned up.

@Lupp is right about iterating if a person had to distinguish other text from “”, as far as I can see. queryFormulaCells(2) will definitely return cells with “” along with any other non-numeric result (other than an error, I suspect, which is 4).