We help IT Professionals succeed at work.

sorting visible rows with auto-filter applied

amangopal asked
is it possible to sort rows while the autofilter feature is applied.

meaning i apply the autofilter feature and filter some rows (say out of 100 rows 30 are visible after column C has been filtered for a value)

can i then apply a sort on the visible rows only between the range say column A to column K with only the visible filtered rows selected (header and other area not selected, assuming i do not care whether the invisible / hidden rows get sorted or not, though ideally i would not want to change anything in the hidden rows)

is there a macro workaround available ? i do not want to write a bubble sort (i am not much good at any other good sorting algo..) on the visible rows, as there will be cases where i may have about 6000 visible rows (on a total data of about 30000)

i am using ooo 3.0 (65K rows max, i think .. please correct me if i am wrong there..)


Watch Question


I am not aware of any currently built macros that can do this.  I believe I can create one.  However, I don't have a large chunk of time to write this.  I will work on it as I get a chance of the next few days - maybe week and see what I can come up with for you.

Some questions that will determine the complexity of the Macro:
How Many columns are in the sort?
Will the number of columns ever change?
What column do you want to sort by?  
Can I default the sort to use that column?

- Bear


1) the number of columns can not be fixed..  the starting column and the ending column would be variable.  however, let us say that a maximum of 50 columns would be there.  having said that, the routine would need to ensure that it does not sort beyond the selected number of columns as there might be a different set of data in the following columns that the user may not want to sort.  so, essentially, the "swap row" routine would require to have start_col, end_col variables that get initialised by some method / function that returns the starting column of the selected range and the ending column of the selected range

2) yes.. the number of columns would change depending upon the user and the worksheet the user is working on

3 & 4) hmmm... good question and i see how that could make the macro complex..  i know i do not want the user to select the column number by which he would like to sort (that would mean introducing a form and drop down lists etc.. not something i would like anybody to undertake for this kind of a requirement).  in general, i would say that 80% (and "72% of all statistics are generated instantly of the top the head") of the time, the user would be sorting based upon the 1st column.  but i know of a lot of cases where i would not be able to fix that.  i would like the macro to sort based on a button press, so how can we specify the column / columns to concatenate and compare for the sort ?  as a parameter to the macro maybe ?  or should we say that an "*" (asterix) in the column header would mean that the column needs to be concatenated into the sort criteria, and scan all column headers from left to right concatenating them as we go to create the comparision string array.
I have started working on this and will see what I can come up with.  Note sure where I will end up with for selecting the sort columns.  I might use your idea of the column header with an *.  I will see what is easier to program.  

A couple more questions --
Will all items being displayed after the filtering be sorted or will the user be selecting the range to sort?
Will there always be a header row?



there will always be a header row.  however, usually the header row will never be within the range selected for sorting (as it will have the autofilter buttons on within it)

i am guess that always all the items being displayed after the filter would be sorted.  however, considering the fact that the filter itself could be applied only to a partial number of rows, the rows below the filter should not be sorted unless the user explicitly sets so..

besides, i think it does not increase the complexity of the programme keeping it user selection and dynamic rather than "all visible rows" .. what do you think ?
The code will vary greatly on whether I sort all visible cells or all selected cells.  For now, based on what you have stated I will head down the path of all selected cells.  It will probably be a couple days again before I can hack at this.  I will let you know when I have something more.

- Bear

I wanted you to know that I have not forgotten about you.  Been a little more busy than I expected.  Still working away on your code.  Hopefully sometime this week I can finish it.

One more question for you.  Can I assume it should always sort in ascending order?  If not is it always descending order?  Does the user need to choose?
Let me know.  Right now I am coding for always ascending order.

- Bear


thanks for the comment
let us assume that he will always sort in ascending.
OK, I think I finally have something for you to try.  This was a little more messy than I originally thought.  I have a macro for you to try.  Since it has a dialog box, I can't just post the code.  A zip file with an OOo extension that will load most everything is located at https://filedb.experts-exchange.com/incoming/ee-stuff/7534-SortFilteredRows.zip

To Load Extension:
Unzip the file
In Calc, go to Tools >> Extension Manager
Click Add
Browse to the extracted SortFilteredRows.oxt file
Click Open
[The Macro is now loaded]

To Use Macro:
Filter your rows, select cells to be sorted (You must select a range of cells or Macro will not run).

Run Macro:
  • Tools >> Macros >> Run Macro
  • My Macros >> SortFilteredRows >> modSort
  • Select SortFiltered
  • A dialog box will display.  If the header row has been selected in the range check the Header row box.
  • Choose the column you want to sort by. (You can sort Ascending order by one or two different columns)
  • Click Sort
Optional Way to Use Macro (Recommended Method)
Assign Macro to a Hot Key or a Toolbar Button.
Tools >> Customize
For Toolbar
Click Toolbar Tab
Click Add
In Category Scroll Down to OpenOffice.org Macros and expand MyMacros >> SortFiltered >> modSort
In Commands Select SortFiltered
Click Add
Now use the arrows to position on your toolbar
Click Modify >> Change Icon
Scroll Through the list and find the AZ Sort button

For HotKey
Click Keyboard Tab
Click Key sequence you want to use
In Functions >> Category scroll to OpenOffice.org Macros and expand User >> SortFiltered >> modSort
In Function click on SortFiltered
Click Modify

WARNING:  I have tested this some, but please, please make a back up of your files before testing.  Let me know what you find.




thanks for the compilation, i am currently travelling will test it out by tuesday and get back to you.  will try to sneak in time before that since i am so excited about trying it.  but give me till tuesday to get back to you.



the expert not only worked on the problem but also ensured that the deployment strategy was easy.  especially appreciate the thought of making sure that i had an easy way to deploy the macro (recommended method).  the solution was more than what i was looking for thanks a lot