Then the OpenOffice Error dialog box appearsĪ Scripting Framework error occurred while running. Step 4 - I clicked inside the Library panel > My Macros > Standard > Module 1 then the CentralReflection appears inside Macro name panel Step 3 - I clicked the Tools menu > Macro > Run Macro Step 2 - I blocked (high-lighted) column B (only the lotto draw results) Step 1 - I used Alt + F11 to insert Lupp's script using the Edit button. I will list what I did step by step and please let me know what went wrong in each step: I tried both the VBA script and the Python macros but l must have made mistakes so they cannot flip. I missed to mention that flipping and central reflection together with a move to a new location can also be done based on standard functions. Rectangular ranges are what the function is working on.Īn application of the function only makes sense if entered in array mode (Ctrl+Shift+Enter).įlipping a group of columns may either be done for every column one by one or by an adapted routine for axial mirroring. Therefore it was also obsolete to speak of columns or of rows. Usage: I did not make a difference about flipping horizontally or vertically because both the operations were subsumed under the term of central reflection. The range to work on must be passed in another way to the then Sub. Of course, "matu" may rework the code for flipping in situ. I thus got the advantage of an easy way to pass the range as parameter for free. REM A rejected alternative was to limit the loop for b conditionally.Īs I prefer to not manipulate data in situ based on custom programming but to create a function occupying an output range, I did it this way. REM Including this in the above loop for a would mirror twice and thus lead back to the original middle row. REM Only in case of an odd number of rows two half rows need mirroring one onto the other. REM The upper half and the lower half of the array are mirrored now over the center excluding the middle row if one exists at all. REM It is applicable to any m x n - range where m, n are integers, the value 1 allowed in both places.ĭim a As Long, b As Long, m As Long, n As Long, aLim As Long, bLim As Long, h REM The function must be used in array mode. VBA: Flip / reverse a range data order vertically in Excel.Ĭode: Select all Function CentralReflection(pRange()) Step 2: Click Insert > Module, and paste the following macro in the Module Window. Step 1: Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window. If you know how to use a VBA code in Excel, you may flip / reverse data order vertically as follows: My question is - do you have a way to run the macro below in OpenOffice so that we can flip numerical data vertically? I understand it can be done in MS Office 2013 with "flip vertically" from its ribbon menu but I want to do it in OpenOffice. I want to flip the vertical column of data with the latest lotto draw results to show at the bottom, how can this be done? You can then copy the new column without the spaces and do a “Paste Special” into the old column, overwriting the text with the spaces.I have a problem to flip data in vertical column (not in ascending/descending order)Ĭurrently the lottery results on the link below are shown with the latest lotto draw results to appear at the top of the table. Drag that function down and it will remove all of the spaces: When you’re done with your function, select “OK” and you’ve got your spaces removed. Simple TRIM the text in B1 and it will get rid of the extra spaces: The function you want is in the Text Category (use the dropdown menu) and is called TRIM. Click in cell C1 and then go up to Insert -> Function. If you want to get rid of the extra space, there is a LibreOffice Calc function for that. Once you’re done, hit “OK” and you’ll see your single columns split into two: However, I’m going to leave the extra space and show you one more function that can be useful in more complex situations. I selected just “Tab” and “Comma” but could also select “Space” to get rid of the extra space. This window gives you several options for splitting the cells, using commas, spaces, semicolons, tabs, other, etc. Select the column that you want to split: Open your spreadsheet with the cells that need to be split, like this: So, here’s a quick tutorial on how this is done. I figure it out every time, but then I forget how I did. I periodically have to take a column of text in LibreOffice calc that has names like this “Lastname, Firstname” and split them into two columns.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |