## Intro

Four previous posts were a journey around Excel's SUBTOTAL() worksheet function

- It started with a need for someone to calculate averages from a row, ignoring only data in hidden columns
- A complement to SUBTOTAL() was provided.
- A replacement for SUBTOTAL() and it's complement
- A recommendation to overcome a shortcoming of the Excel object model

## Extravagant Boasts or Will Sonnett time?

This post has a User Defined Function that- Makes SUBTOTALx() functions, both the Excel Worksheet function and the User Defined Functions provided in this series, obsolete.
- Can turn many worksheet functions that use arrays or ranges into one of those sub-functions of SUBTOTAL(), i.e. working while ignoring hidden data, without
- Extending the list of numbers to cover all applicable Worksheet functions. It's tough enough to remember 1-11 and 101-111. Those are in alphabetical order and any new worksheet Function from Microsoft would be given the next higher number...then there is overlap with the 101-111...Too much of a mess to try!
- Forcing users to type in the name of a Worksheet function as the first argument.
- Tested functions include: QUARTILE.EXC() & QUARTILE.INC(), PERCENTILE.EXC() & PERCENTILE.INC(),
- It does not work
- When the Worksheet function needs a contiguous range.
- When using a LOOKUP() function or it's relatives, e.g. MATCH() and INDEX()
- Conditional functions such as : COUNTIF(), COUNTIFS(), SUMIF() and SUMIF()

## The Function

As with the previous functions, this one walks through the ranges specified and includes them in the resulting range if the row and column are visible. If the cell has already been included in the resulting range, the Union has no effect.

Function VisibleRange(ParamArray rngFull()) As Range

Dim rngVisible As Range

Dim rngCell As Range

Dim dblI As Double

For dblI = LBound(rngFull) To UBound(rngFull) ' Considers each range

For Each rngCell In rngFull(dblI).Cells ' Considers each cell in the current range

If Not (rngCell.Rows(1).Hidden Or rngCell.Columns(1).Hidden) Then

If rngVisible Is Nothing Then

Set rngVisible = rngCell ' Establishes the selected range

Else

Set rngVisible = Union(rngVisible, rngCell)

' Adds 2nd and subsequent cells to the output

' Adds 2nd and subsequent cells to the output

End If

End If

Next rngCell

Next dblI

Set VisibleRange = rngVisible ' Assigns the collected range to the Function

End Function

## How to use it

The primary way to use VisibleRange() is to include it within other functions, e.g. =SUM(VisibleRange(B2:D24). It is not limited to a single range. Here are some variations that have been tested

- With disjoint ranges, e.g. =SUM(VisibleRange(B2:D24,X2:AA24, M2:M24))
- Without regard for the same number of rows or columns in the disjoint ranges, e.g. =SUM(VisibleRange(B2:D24, T4:T5, E7:G7))
- With overlapping ranges, =SUM(VisibleRange(B2:D24, T4:T5, D7:G12)), the cells that are identified in more than one of the ranges (D7:D12 in this case) are used only once
- Without regard for the same number of rows or columns in the disjoint ranges, e.g. =SUM(VisibleRange(B2:D24, T4:T5, E7:G7))
- With Named ranges, e.g. =SUM(VisibleRange(DataSet1, DataSet2))
- With Named ranges on multiple worksheet, e.g. =SUM(VisibleRange('Clyde Study'!DataPoints, 'Selma Research'!DataPoints))

## No comments:

## Post a Comment