amangopal
asked on
sorting visible rows with auto-filter applied
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..)
thanks,
regards,
aman
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..)
thanks,
regards,
aman
ASKER
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.
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?
-Bear
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?
-Bear
ASKER
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 ?
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
- Bear
amongopal,
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
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
ASKER
thanks for the comment
let us assume that he will always sort in ascending.
let us assume that he will always sort in ascending.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi
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.
thanks
aman
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.
thanks
aman
ASKER
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
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