Sort and filter a range

You can sort data in Visual Basic for Applications (VBA) using the Sort method, and in AppleScript by the identical sort command. If a single cell is specified, the current region is sorted (the area around the cell bounded by both blank rows and columns). The following snippet sorts the current region around cell A1 based first on the values in column A, then on the values in column B:

With ActiveSheet
	Range("A1").Sort _
		Order1:=xlAscending, _
		Key1:=.Columns("A"), _
		Order2:=xlAscending, _
		Key2:=.Columns("B"), _
		Header:=xlNo, _
		MatchCase:=False
End With

It's just the same in AppleScript, recalling that match case true will compile to with match case.

tell application "Microsoft Excel"
	tell active sheet
		sort range "A1" order1 sort ascending key1 column 1 ¬
			order2 sort ascending key2 column 2 header ¬
			header no ¬
			without match case
	end tell
end tell

You cannot refer to column A in AppleScript; the script will error. Your choices are column 1, column "A:A", or range "A:A".

sort range "A1" order1 sort ascending key1 range ¬
	"A:A" order2 sort ascending key2 range "B:B" header header no ¬
	without match case

Filter a range using autofilter

Each worksheet contains an Autofilter object, and individual autofilters are applied using the AutoFilter method. Each filter can have two criteria, and filters are cumulative. This example causes the sheet to display only rows in which the value in column A is >=10 and <=100, and the value in column C is OK.

With ActiveSheet.Range("A1")
	.AutoFilter 'remove existing autofilter dropdowns
	.AutoFilter _
		Field:=1, _
		Criteria1:=">=10", _
		Operator:=xlAnd, _
		Criteria2:="<=100", _
		VisibleDropdown:=True
	.AutoFilter _
		Field:=3, _
		Criteria1:="OK", _
		VisibleDropdown:=True
End With

Note that the autofilter is applied to the CurrentRegion around Cell A1, since only one cell is specified.

In AppleScript:

tell application "Microsoft Excel"
	tell range "A1" of active sheet 
	--single cell, applies to current region
		autofilter range 
		-- with no parameters, toggles dropdowns
		autofilter range field 1 criteria1 ">=10" operator ¬
			autofilter and criteria2 "<=100" ¬
			with visible drop down
		autofilter range field 3 criteria1 "OK" ¬
			with visible drop down
	end tell
end tell

The only difficulty here is in finding the right autofilter term. You need to know your VBA well enough to know that the .AutoFilter in the macro is not the property, which applies to a worksheet and returns the AutoFilter object for the sheet, but the method, which applies to a Range. So never mind the autofilter class or the autofilter object property of sheet in Microsoft Excel Suite. Look in the Table Suite, where the class being targeted — range — resides, and you'll find the autofilter range command (analogous to a method). Everything else is identical.

Your feedback was not submitted because the network connection was lost. Verify that you have an Internet connection, and then try submitting your feedback.

If you are still unsuccessful, the feedback server might be temporarily disconnected. Try submitting your feedback later.

Developers

Sort and filter a range

Filter a range using autofilter