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

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:

BOARD & CARD GAMES
FICTION
HOBBY
LIVING CARD GAMES

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

BOARD & CARD GAMES
HOBBY
LIVING CARD GAMES
FICTION

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!

Maybe Custom Sort List feature can help?

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.

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.

Click Ok to perform sorting

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

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!

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.