Skip to main content

Filter and Sort (Data Filtering, Multi-condition Sorting)

APIDescriptionExample
Range.AutoFilterFunction that sets or removes auto filter for the specified rangelet range = instance.Application.ActiveSheet.Range("A1:B2");

range.AutoFilter({
Field: 5,
Criteria1: ">2000",
Operator: instance.Enums.XlAutoFilterOperator.xlAnd,
Criteria2: "<=3000"
});
Worksheet.AutoFilterProperty that returns the auto filter object set for the specified sheetlet sheet = instance.Application.ActiveSheet;

let autoFilter = sheet.AutoFilter;
Worksheet.AutoFilterModeProperty that returns whether auto filter exists on the specified sheetlet sheet = instance.Application.ActiveSheet;

console.log(sheet.AutoFilterMode);
Worksheet.SortProperty that returns the sort object for the specified sheetlet sheet = instance.Application.ActiveSheet;

let sort = sheet.Sort;
AutoFilter.FilterModeProperty that returns whether the auto filter is enabledlet autoFilter = instance.Application.ActiveSheet.AutoFilter;

console.log(autoFilter.FilterMode);
AutoFilter.RangeProperty that returns the target range object of the auto filterlet autoFilter = instance.Application.ActiveSheet.AutoFilter;

let target = autoFilter.Range;
AutoFilter.SortProperty that returns the sort object of the auto filterlet autoFilter = instance.Application.ActiveSheet.AutoFilter;

let sort = autoFilter.Sort;
AutoFilter.ApplyFilterFunction that applies filtering of the auto filterlet autoFilter = instance.Application.ActiveSheet.AutoFilter;

autoFilter.ApplyFilter();
AutoFilter.ShowAllDataFunction that shows all data of the auto filterlet autoFilter = instance.Application.ActiveSheet.AutoFilter;

autoFilter.ShowAllData();
Sort.HeaderProperty that returns or changes whether the first row contains header informationlet sort = instance.Application.ActiveSheet.Sort;

console.log(sort.Header);
sort.Header = true;
Sort.MatchCaseProperty that returns or changes whether to sort case-sensitivelylet sort = instance.Application.ActiveSheet.Sort;

console.log(sort.MatchCase);
sort.MatchCase = true;
Sort.OrientationProperty that returns or changes the sort orientationlet sort = instance.Application.ActiveSheet.Sort;

console.log(sort.Orientation);
sort.Orientation = instance.Enums.XlSortOrientation.xlSortColumns;
Sort.RngProperty that returns the sort target range objectlet sort = instance.Application.ActiveSheet.Sort;

console.log(sort.Orientation);
sort.Orientation = instance.Enums.XlSortOrientation.xlSortColumns;
Sort.SortFieldsProperty that returns the object that manages the sort informationlet sort = instance.Application.ActiveSheet.Sort;

console.log(sort.Orientation);
sort.Orientation = instance.Enums.XlSortOrientation.xlSortColumns;
Sort.ApplyFunction that performs sortinglet sort = instance.Application.ActiveSheet.Sort;

console.log(sort.Orientation);
sort.Orientation = instance.Enums.XlSortOrientation.xlSortColumns;
Sort.SetRangeFunction that changes the sort target rangelet sort = instance.Application.ActiveSheet.Sort;
let target = instance.Application.ActiveSheet.Range("C1:D2");

sort.SetRange(target);
SortFields.CountProperty that returns the number of sort information entrieslet sort = instance.Application.ActiveSheet.Sort;
let sortFields = sort.SortFields;

console.log(sortFields.Count);
SortFields.ItemFunction that returns a sort information object at a specific indexlet sort = instance.Application.ActiveSheet.Sort;
let sortFields = sort.SortFields;

let sortField = sortFields.Item(1);
SortFields.AddFunction that adds sort informationlet sort = instance.Application.ActiveSheet.Sort;
let sortFields = sort.SortFields;
let key = instance.Application.ActiveSheet.Range("A1");

sortFields.Add({
Key: key,
Order: instance.Enums.XlSortOrder.xlAscending
});
SortFields.ClearFunction that removes all sort informationlet sort = instance.Application.ActiveSheet.Sort;
let sortFields = sort.SortFields;

sortFields.Clear();
SortField.KeyProperty that returns the range object that is the key for sortinglet sort = instance.Application.ActiveSheet.Sort;
let sortField = sort.SortFields.Item(1);

let key = sortField.Key;
SortField.OrderProperty that returns or changes whether sorting is ascending or descendinglet sort = instance.Application.ActiveSheet.Sort;
let sortField = sort.SortFields.Item(1);

console.log(sortField.Order);
sortField.Order = instance.Enums.XlSortOrder.xlDescending;
SortField.PriorityProperty that returns or changes the sort prioritylet sort = instance.Application.ActiveSheet.Sort;
let sortField = sort.SortFields.Item(1);

console.log(sortField.Priority);
sortField.Priority = 1;
SortField.DeleteFunction that deletes the sort informationlet sort = instance.Application.ActiveSheet.Sort;
let sortField = sort.SortFields.Item(1);

sortField.Delete();
SortField.ModifyKeyFunction that changes the range that is the sort keylet sort = instance.Application.ActiveSheet.Sort;
let sortField = sort.SortFields.Item(1);
let key = instance.Application.ActiveSheet.Range("B1");

sortField.ModifyKey(key);

Data Processing , Built-in Function Libraries , Auto Complete , Generate and Edit Pivot Table , Data Validation , Find and Replace