Table Sorting, Table Filtering and Table Total Row Calculations (using VBA)

Learn how to Sort, Filter and use the Total Row so that you can write VBA code to enhance your spreadsheets using Excel Tables.

In this 23 minute video we cover:

• SORTING TABLES
  • How to use the ListObject.Sort.Sortfields Object to set the sort key (combined with .Add method to add a new sort, and .Clear method to clear existing sort)
  • The ListObject.Sort.Sortfields.Add options for setting the SortOn parameter (to sort on Cell color, Font color and Values) and Order parameter (to sort Ascending or Descending)
  • How to apply the new sort using ListObjects.Sort.Apply
• FILTERING TABLES
  • How to use the ListObject.AutoFilter property to check if a Table is filtered
  • How to use the ListObject.AutoFilter.ShowAllData method to reset an existing filter
  • How to filter a Table range using the ListObject.Range.AutoFilter method
  • The ListObject.Range.AutoFilter options for setting the Operator parameter (xlAnd, xlOr, xlTop10Items, xlBottom10Items, xlTop10Percent, xlBottom10Percent, xlFilterValues)
  • Examples of filtering a table for entries starting with a given letter (e.g. Names starting with "c", or Surnames starting with "g")
  • How to filter for more than two criteria using Array() (e.g. Filtering a column for all entries including "Apple", "Banana", "Cherry")
  • Example of filtering a table for entries greater than a number (e.g. numbers > 70)
• CALCULATED TOTAL ROW
  • How to change the Total Row calculation by setting the property ListObject.ListColumns(N).TotalsCalculation
  • Options for TotalsCalculation (None, Sum, Average, Count, Count Numbers, Min, Max, Standard Deviation, Variance, Custom)

Write your comments and feedback below

• If you have questions or comments about this lesson, write it in the section below.
• Alternatively you can email the instructor Victor at [email protected]