Excel clear sortfields and sort orientation

Hi Guys,

I’m trying to write a dynamic custom sort routine but my script is failing if the current sort orientation is set to rows rather than columns. Does anybody have any idea how I can clear the current sort using the clear sort fields command and reset the orientation of the sort from rows to columns?

The below script works if I manually clear all of the sort fields in the active worksheet and change the sort options to ‘Sort top to bottom’ rather than ‘Sort left to right’. If I don’t do this my script errors:

tell application "/Applications/Microsoft Office 2011/Microsoft Excel.app"
	set myref to active sheet of active workbook
	set rowCount to count of rows of used range of myref
	set columnCount to count of columns of used range of myref
	
	set storeDataRow to 8
	set SKUdataColumn to 12
	
	set Sort_Object to sort object of myref
	
	repeat with i from SKUdataColumn to columnCount --> I need to dynamically add the columns to be sorted as the number of columns will vary
		set sortStart to get address of cell storeDataRow of column i
		set sortEnd to get address of cell rowCount of column i
		set mySortRange to sortStart & ":" & sortEnd
		add sortfield (sortfields of Sort_Object) key range (get address of column i of myref) order sort descending
	end repeat
	
	set sortStart to get address of cell storeDataRow of column 1
	set sortEnd to get address of cell rowCount of column columnCount
	set mySortRange to sortStart & ":" & sortEnd
	log mySortRange
	set sort range Sort_Object rng range mySortRange --> this is the range of cells that need to be sorted
	apply sort Sort_Object
end tell

Thanks,
Nik

Hi Guys,

I think I’ve worked out how to do this by adding the below lines:

	set sort orientation of Sort_Object to sort columns
	
	set sortfieldList to every sortfield of Sort_Object
	repeat count of sortfieldList times
		delete sortfield item 1 of sortfieldList
	end repeat

It’s taken me a while to work this out but hopefully it may help others that have similar problem. This is my full code:

tell application "/Applications/Microsoft Office 2011/Microsoft Excel.app"
	set myref to active sheet of active workbook
	set rowCount to count of rows of used range of myref
	set columnCount to count of columns of used range of myref
	
	set storeDataRow to 8
	set SKUdataColumn to 12
	
	set Sort_Object to sort object of myref
	set sort orientation of Sort_Object to sort columns
	
	set sortfieldList to every sortfield of Sort_Object
	repeat count of sortfieldList times
		delete sortfield item 1 of sortfieldList
	end repeat
	
	repeat with i from SKUdataColumn to columnCount --> I need to dynamically add the columns to be sorted as the number of columns will vary
		set sortStart to get address of cell storeDataRow of column i
		set sortEnd to get address of cell rowCount of column i
		set mySortRange to sortStart & ":" & sortEnd
		add sortfield (sortfields of Sort_Object) key range (get address of column i of myref) order sort descending
	end repeat
	
	set sortStart to get address of cell storeDataRow of column 1
	set sortEnd to get address of cell rowCount of column columnCount
	set mySortRange to sortStart & ":" & sortEnd
	log mySortRange
	set sort range Sort_Object rng range mySortRange --> this is the range of cells that need to be sorted
	apply sort Sort_Object
end tell

Thanks for looking,
Nik