Using a macro to sort a mutliple ranges in a workbook

[verus172]

I am a newbie. I have tried to develop a macro, but was unsuccessful. I was hoping someone could help me.

Unfortunately, Libreoffice doesn’t have a sort function. This the functionality I am trying to create and automate through a workbook on multiple sheets.

I need a macro to sort a number of ranges throughout the spreadsheet, and place the sorted data in a column next to the source data.

Sheet1.A1=pineapples
Sheet1.A2=oranges
Sheet1.A3=pears
Sheet1.A4=bananas
Sheet1.A5=broccoli
Sheet1.A6=lemons
Sheet1.A7=strawberrries
Sheet1.A8=apples

I need a macro automatically sort this range and place the results Sheet1.B1:B8

I need to do this for multiple (50) ranges through a workbook. I assume that I just copy the macro code for the first range and change the input range and output range. Or can it be simply with some type of array and For loop which might make it easier to manage.

I would be most grateful for any assistance. I have tried, but am not getting anywhere.

[JohnSUN]

That is, there is no word “Fruit” in cell A1? In other words, does your data range contain no header? Or did you just forget to mention it?

[verus172]

The range could contain a header row. I anticipate the macro will utilise the “Sort range” (with all its parameters) function from the menu. There is a parameter which specifies whether the input range contains a header. Maybe the macro needs to specify the additional parameter which specifies whether the input range includes a header. For my purposes, the header is simply above the input range and out range and I don’t need the macro to look after header handling. Thank for your quesiton.

[JohnSUN]

One more clarification, please. In your example, the range contains only one column and here the sort order is clear (ascending). Should this macro sort ranges with multiple columns? Do we need to specify the order of the sorted columns in this case? Is it always only ascending?

[verus172]

In this case, there is only one column that needs to be sorted. I don’t have a need for a multiple column sort, or a multi-column output.

My need is for ascending. The order does not need to be specified.

Thank you.

[JohnSUN]

I didn’t actually ask clarifying questions - I just listed the options that the built-in sort can use. I tried to lead you to the idea that the built-in mechanism can perform the main work of sorting. If you use Define Range to describe each of the ranges to be sorted, and then use the options on the Options tab to specify where to move the sorted range, then the problem will be almost completely solved.

[verus172]

I understand the built in sort option and that I can manually move through all the specific ranges. The problem is that there are more than 50 ranges. A manual method is time consuming and not reliable. Thus, my request for help with a macro that I could specific the input-ranges and output-ranges and simply run as required.

[JohnSUN]

Well, look at this option for solving the problem - SortRangeToAnotherLocation.ods.

Since the function cannot change any cell on the same sheet where it is located, all calls to the sort function are placed on a separate sheet - when they are executed there, the ranges are sorted here.

The function has only one parameter - the cell from which the range to be sorted begins. Only one column is always sorted, always only in ascending order, the result is always placed in the adjacent column.

To re-sort all choosed ranges press Ctrl+Shift+F9

[verus172]

Thank you for this! I appreciate it! Unfortunately, I can’t get it to work at this end. I have opened the spreadsheet. I press Ctrl-Shift-F9 and there is no sorting, no messages, nothing happens.

I also tried to select Tools - Macro - Run macro. I selected “SORT”, “sortrange”, “getranges” and nothing happended (ie no message, no sort).

I can confirm that JRE is installed. Any ideas as to why it may not work at this end? I am most grateful for any assistance.

[verus172]

I have changed the Macro security settings. Changed to Low. Added folder path as a truster place. Close workbook. Opened workbook.

It partially works.
Sheet2.D5 works … range sorted and output to adjacent cells.
Sheet1.A1 not working
Sheet1.D6 not working
Sheet1.G9 not working

I then changed some protect sheet settings. Partially worked.

I then duplicated “sheet2” and it all started working.

An apology. Sorry. Your file did work and it is something in my setup that prevented it working.

[verus172]

I am so grateful for this. Thank you!

[verus172]

I was hoping I could trouble you (or the community) for one modification.

If the source column is a formula, then I need the adjacent output column to be raw text-numbers. It currently pastes a formula into the output column.

To clarify, column C is to be sorted:
Sheet1.A1=“two”;B1=“pears”,C1=A1&" “&B1
Sheet1.A2=“one”;B1=“oranges”,C2=A2&” “&B2
Sheet1.A3=“four”;B1=“watermelon”,C3=A3&” “&B3
Sheet1.A4=“three”;B1=“bananas”,C4=A4&” "&B4

If I sort this by specifying Sheet1,C1 in SortOptions then I need the following result (in column D):
Sheet1.D1=“four watermelon”
Sheet1.D2=“one oranges”
Sheet1.D3=“three bananas”
Sheet1.D4=“two pears”

The output is text not the original formula.

Thank you

[JohnSUN]

@verus172 This complicates the task somewhat. One of the possible solutions is to take the values ​​of the calculated formulas into an array, sort it programmatically and place it in the right place. But you can do otherwise - before sorting, copy all the formulas in the range into an array (just remember for a while), replace all formulas in the range with their values. Now you can sort the range and place the sorting result (values, not formulas) in the right place. Now restore the formulas from the array. The function will increase by only three lines.