Beyond SUBTOTAL()

Overview

This collection of posts deal with discoveries about the SUBTOTAL() worksheet function and some means to improve it by replacing it...and some ancillary thoughts.

The Posts...to date

• AVERAGE() UDF excluding hidden columns...plus SUM(), COUNT(), etc
  How to get get the AVERAGE() function to look across a row and ignore hidden columns.  Extend that code for other functions.
• Interpretation of SUBTOTAL() operations argument Discussion of the oh-so-poorly described differences between the two ranges of arguments of the SUBTOTAL() function.
• SUBTOTAL()-like UDF for rows with hidden columns
  Adding a Select Case structure to the AVERAGE()-like UDF to create a complement to SUBTOTAL().
• SUBTOTAL()-like UDF for cases when Rows and Columns may be hiddenChanging a line of codeand the SUBTOTAL()-like UDF for rows with hidden columns now works whether rows or columns are hidden.
• Shortcoming of Object Model Affects SUBTOTAL()-like UDFs
  Excel has an event for hiding rows.  There is no such event when columns are hidden.  This post discusses work-around to keep the new SUBTOTAL() function current with the data  
• SUBTOTAL() no longer needed!
  Taking the lessons-learned from building the complement and replacement for SUBTOTAL() to create a function that extends the "if it's hidden ignore it" approach to many worksheet function that refers to ranges.

Post to be developed

Defining a Range by the Functions in Cells

This post no longer deals directly with the replacement of SubTotal(). Named ranges are wonderful things, but keeping them up-to-date adds to maintenance and development overhead.  They are referenced in the above and while writing the other posts I realized there are times with we make an entry into a worksheet and we need to expand or contract the scope of a named range.

This post, still in draft form, puts forth the idea that when a cell is updated the event model can be exploited to add/remove the changed cell to/from a Named range, typically scoped to the worksheet.