Lotus 123 slash command

Discussions about using 3rd party extension with OpenOffice.org

Re: Lotus 123 slash command

Postby Villeroy » Wed Dec 03, 2014 10:39 pm

Arrow keys move by one cell
Ctrl+Arrow moves block wise from a blank to the next non-blank or from a non-blank to the end before the next blank
PageUp/Down one screen full of cells
Ctrl+PgUp/Down moves in the 3rd dimension between sheets
Home jumps to column A in this row
Ctrl+Home jumps to A1
End to the end of this row within the used range
Ctrl+End jumps to the last (bottom-right) cell of the used range
All this works similarly in any browser, text editor, file manager and thousands of typical Windows programs.
And now the big surprise: Like with any other typical Windows program since Windows '95 you can use any of the above shortcuts together with the shift key in order to expand a selection. SO you do not call a selection command to select something by keyboard. You simply use comon keystrokes to actually select cells.

Within a selected cell range:
Tab moves row wise
Enter moves column wise (unless you specified another move in the options dialog)
Shift+Tab and Shift+Enter move backwards in their respective direction, e.g. Shift+Tab moves from the first (top-left) cell to the last (bottom-right) cell.

With my above mentioned customization, I copy the current region's data to the next sheet:
Ctrl+A (select current region of non-blanks)
Ctrl+C (copy to system clipboard)
Ctrl+PgDown (next sheet)
Insert key calls the paste-special dialog
Enter confirms the default selection (numbers, dates, text, no formulas nor formatting)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26385
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lotus 123 slash command

Postby lloydy11 » Sat Apr 09, 2016 8:58 pm

I am nearly 10 years too late to this thread, but find myself compelled to reply. I have been using spreadsheets for nearly 20 years – Lotus 123 from 95-98 and Excel from 98 to date.

I consider myself to be an expert Excel user, and am well adept at using mice (with/without rollers), and pointers and pads on laptops to highlight ranges, but still find myself using keyboard commands for some things - mainly <ctrl + c> and <ctrl + v> for copy and paste and <ctrl + f> for find and replace, and <ctrl + [ > to move to cell precedents.

This is partly because I am at the age (43) where I can’t be bothered to learn new things – when we moved to Excel in 98 I read the Excel Bible from cover to cover, in excel 2003 I had all the menu bar icons exactly how I wanted them, including custom designed buttons with macros assigned to them). I can just about find my way around the Excel 2010 menus and don’t even get me started on 2013 – thank god it is not only on my home laptop.

But it is mainly because I find it so much faster – as someone alluded to in about 2011, when you live in spreadsheets for your job, these little things can make a lot of difference – even if not material in time, for state of mind.

I am well versed at using copy and paste special (through top menus, right clicks to bring up menus etc) but today really wanted, but my main issue nowadays is that using so many different computers, all the keyboards are subtly different, so I find myself putting caps lock on all the time or deleting things when trying to page up and down, and I don’t like using laptop pads/pointers as much as I do using arrow keys and page-up down and keyboard commands.

So today, after a few misplaced keyboard commands on my new laptop, I found myself googling /rv and came across this.
I find myself compelled to write such a long introduction, to try and reduce the amount of vitriol that will come my way for being such a dinosaur. I have never seen such bitchiness on a website – as someone mentions above, the guy asked a question. Could nobody just answer the question? A simple “no” would have sufficed, or some helpful suggestions and save the rest for mumsnet?

My answer is “no”.
I have found nothing as quick as /rv to range value in Excel. The closest I have found is rather than <ctrl + c> and <ctrl + v> for copy and paste, using <ctrl + alt + v> to bring up the paste special menu, then select Values.
For some reason in Excel 2010, when using a normal mouse I find it quickest to use <ctrl + c> and then the paste special menu from the menu bar. In 2003 and before, I had added the paste values icon to the top menus (and made my own button for paste-formulas with a nice pixelated F on it)

Here are some other tips I use for copy and pasting that older users my find useful :
1/ To highlight ranges, I have always used the shift keys and arrow keys, rather than a mouse. I find it so much easier as they are normally all close together on the right hand side,

Simply start at the top left of your range. Hold down shift and use the right and down arrow keys to navigate to the bottom right. For larger ranges use page down (and page up if you shoot too far) rather than the arrow keys.

Regular Excel users will also know that if you press the “End” key then an arrow key, you will automatically move to the ends of the range. So with a normal keyboard, holding down shift and pressing the keys <End>, <right arrow>, <end>, <down arrow> can almost instantaneously have a range highlighted to copy and paste in your preferred manner without having to move your right hand - apart from the fingers, of course. The issue with my new laptop is the end button is now up by the function keys and not down by the arrows, so I have to use my left hand on the end button to do it with any speed, but it is incredibly quick on a normal keyboard

Regular Excel users will also note this will only work if data is continuous gaps in the columns of the top row or missing rows below will cause the highlighting to stop at that point. However repeatedly pressing <end> then <down> will move through each section (or use page down/up or even drag the range down using the scroller on the right if needs be)

2/ I got cheesed off today navigating to the paste special menu all the time and miss-keying <ctrl + alt + v>, so I wrote the macro* below which does the same as the old /rv in Lotus:

Sub SlashRV()
‘First select the range to paste as values
Selection.Copy
‘This copies the selected area
Selection.PasteSpecial Paste:=xlPasteValues
‘This pastes as values
Application.CutCopyMode = False
‘This clears the cut/copy moving border (not sure what this is called) – it is the equivalent of pressing the Escape key
End Sub

I couldn’t assign it to the / key, so assigned it to m instead, so now highlighting a range and pressing <ctrl + m> will range value it for me – that’s as close to /rv as I can get, and I love it already.

3/ Nothing to do with the above, but one other thing I really dislike in Excel is the default setting for the number formatting “,” (comma) icon
Ninety-nine times out of one-hundred I prefer my numbers to have brackets for negative numbers, and I hate the way it left-aligns the minus sign in the cell, which is a particular pain when cells are wide – you can have the minus sign appearing right next to the number on the left (which are right-aligned by default) and miles away from the number in question; this is especially annoying when you print out pages and don’t have borders around the numbers.

So I use this macro* to make my numbers nicely comma/bracket formatted (and then use the increase decimal icon if need pence/cents as well as £/€):

Sub number()
Selection.NumberFormat = "#,##0;(#,##0);0"
End Sub

In earlier Excel, I assigned it to the existing “,” icon, but in Excel 2010, I just have is assigned to <ctrl + n> for number, as I rarely need to open a new file in a hurry.

4/ Again nothing to do with above, but I often have large multi-tabbed spreadsheets that I want to tidy up before sending to other people, by hiding raw data/calc tabs rather than removing formula and deleting sheets:

Sub Hide_Sheet()
Hide_Sheet Macro
ActiveWindow.SelectedSheets.Visible = False
End Sub

This just hides the sheet you are in (regardless of its name). I ususally assign this to the “h” key for hide, and it can be very useful for quickly hiding 20 tabs or so (please refrain from commenting on why I might need so many tabs, and that I should simplify my business processes instead etc)
Beware – it isn’t quite as easy to unhide tabs with a generic macro that will work on any file you open regardless of what the tabs are called, but back to old keystrokes here – I just go <alt> then “o” “h” “u” (format, sheet, unhide). You then have to select the sheet to unhide, but I often find just hit enter and repeat for all sheets, then <ctrl + h> to re-hide the ones you don’t want is quicker than actually finding the one you want to unhide is quicker

5/ And nothing to do with Excel at all, but here’s a tip for free. If you’re composing an e-mail you’re not sure about sending, don’t add the recipients until you’ve written and reviewed it. That way nothing will happen if you hit “Send” instead of “Save” (fortunately it’s been a few years since I fell foul of that mistake)

* there should be tonnes of posts about recording macros or writing them in VBA and assigning to keystrokes, so won’t go into that here.

I’m probably a few years too late for this thread (especially if someone was 73 in 2008) and the question was actually about OOo, so I might have completely missed the point – if either of these are true feel free to keep it to yourself. Otherwise I trust you found it useful...
OpenOffice 3.1 on Windows Vista
lloydy11
 
Posts: 1
Joined: Sat Apr 09, 2016 8:47 pm

Previous

Return to Extensions

Who is online

Users browsing this forum: No registered users and 2 guests