Hi everyone,
I’m trying to script XL to sort a range of rows (rows 41:456) by column B in ascending order. This must be pretty simple, but I am stumped.
The other way I would like to be able to sort is by the “autofilter-custom filter…” approach. Basically, with the AutoFilter turned on for each of the column headers I would like to sort by the custom items that are in the filter list.
This will sort the visible rows of 41:456
tell application "Microsoft Excel"
sort range "41:456" key1 range "B2" order1 sort descending
end tell
Apply the filter, run that script and and the visible rows will be sorted, but the rows hidden by the filter will be unmoved.
That works great, thanks! Do you know why you have to say “B2”? I understand that we are sorting column B, but why is the “2” necessary?
The 2 is because my test range started at row 2 (rather than the 41 of your problem.)
I’m a little surprised that it didn’t cause an error. I would have expected that the key’s have to be in the range being sorted.
I’m concluding that its not important which row the key is in (i.e. the 2 is not nessesary), but that the key has to be a cell in column B. I’d be leery about assigning a key that is below the top row of the range being sorted.
Ah, that makes sense. Looks like if I change “B2” to “B:B” (i.e., all of column B) that works as well. Thanks for your help!
I have the ascending sort problem solved, does anyone know how to sort rows with custom autofilter?
I’m not sure what you mean “sort with custom autofilter”.
One can script the custom filter and then script the sort (two seperate proceedures).
The userinterface has sorting as part of the menu that comes with AutoFilter, but that is a UI, not a programming proceedure.
Are you having trouble scripting applying the custom autofilter?
What kind of criteira will the data be filtered on?
About the combined process: Starting with all rows visible. The user calls the script.
The script will filter the sheet.
Sort the visible rows, leaving the rest in place.
Do you want only the filtered rows to be visible after the script is run?
or
Do you want all the rows to be visible (after the script), but with the filtered rows re-arranged by the sort?
This will filter the active sheet, with the custom filter that shows only the first half of the alphabet, those rows are then sorted ascending. This assumes that there is a header row in row 41
tell application "Microsoft Excel"
set mySheet to active sheet
set myRange to range "41:456" of mySheet
-- clear any filters
show all data mySheet
set autofilter mode of mySheet to false
-- apply custom filter
autofilter range myRange field 2 criteria1 "<M" operator autofilter and
-- sort the filtered data
sort myRange key1 range "B:B" order1 sort ascending header header yes
end tell
Actually, you called out the issue I’m having - I have been able to script out the sort/custom sort, thanks to the help of your other posts. But I want to script out getting the values of the visible range only (the range of cells displayed by the filter). Does that make sense?
Just so you know exactly what I’m referring to, here’s the code that I’m using as an autofilter:
tell application "Microsoft Excel"
autofilter range range "1:1" field 14 criteria1 "<>"
end tell
So after this, I want to return the visible range of cells, omitting the hidden rows from the result. Any ideas?
Until I get off work and back to my Mac, I’d suggest you look at Special Cells property of a range with the visible cells argument.
Did I read that you are looking for those rows whose column 1 entry is not empty?
Are any of those cells formulas that evaluate to “”?
If not, Special Cells xlCellTypeConstants would be faster than filtering.
Here are examples of visible and constant Special Cells
tell application "Microsoft Excel"
autofilter range range "1:1" field 14 criteria1 "<>"
set visibleCells to special cells (used range of active sheet) type cell type visible
set constantCells to entire row of (special cells range ("N:N") type cell type constants)
end tell