Ask Your Question

Create a macro to Find & Replace, in order to sort a sheet

asked 2019-06-28 07:37:35 +0200

JacksonGumnut gravatar image

I'd like to create a macro that will automatically sort a sheet by a column, according to a specified order.

Column A will initially be sorted alphabetically, including strings such as:


I'd like to sort the sheet by Column A in my own arbitrary order, such as


The easiest way I can see to do this is to create a macro that will Find & Replace those strings to add a number at the start, to allow for numerical sorting.

For example, find "BOARD & CARD GAMES", replace with "1BOARD & CARD GAMES"; find "HOBBY" replace with "2HOBBY", etc. The Find & Replace operation should only look for entire cells.

The macro should then sort the sheet by column A (numerically), then by column B (alphabetically). I don't mind if the sorting numbers remain in the final result.

Is there a way to make a single macro command that will Find & Replace any number of terms based on a list of inputs and outputs? Or will I have make a separate command for each term to be replaced? Or is there another way I can sort the sheet in a desired order, without using Find & Replace?

Thanks in advance for any help!

edit retag flag offensive close merge delete


Maybe Custom Sort List feature can help?

SM_Riga gravatar imageSM_Riga ( 2019-06-29 05:54:00 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-06-29 08:40:12 +0200

Hello @JacksonGumnut

Your "Find&Replace" approach will not work correctly in case you have more then 9 list elements, cause it will return text strings, not real numbers and the further sorting will be made based on the rules for the text strings, which means entry "1. aaa" will then be followed by entry "10. bbb", "11. ccc'', not by "2. ddd" as you would expect.

There is special Custom sort order functionality Calc provides for such tasks. Firstly, you need to create your custom Sort Listto define sorting order. Go to menu item Tools -> Options -> LibreOffice Calc -> Sort Lists and create your own list, placing elements in the order you want them to be sorted. Custom_sort_list_1

Then select range to be sorted and select menu item Data -> Sort. Under Options tab check Custom sort order option and select your predefined list. Custom_sort_list_2

Click Ok to perform sorting Custom_sort_list_3

edit flag offensive delete link more


If this operation is repetitive, the macro recorder supports recording.

Gilberto Schiavinatto gravatar imageGilberto Schiavinatto ( 2019-06-29 17:00:42 +0200 )edit

Very cool, didn't know this was a feature at all. I'll use the macro recorder to set this up.

Thanks for the help!

JacksonGumnut gravatar imageJacksonGumnut ( 2019-06-30 15:00:27 +0200 )edit

answered 2019-06-29 02:03:26 +0200

It could use an additional numbered column, and sort by it, and may be hidden.

image description


ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-06-28 07:37:35 +0200

Seen: 40 times

Last updated: Jun 29