Find the range of a SubTotal

[RWN]

Is it possible to find the range that a SubTotal function refers to?

i.e. cell value is "=SUBTOTAL(9,J25:J31)"

Within the macro I want to determine the range of rows referred to in this cell (25->31).

 

[Dave Peterson]

As long as you're looking for cells on the same worksheet, you can look at
..precedents.

Option Explicit
Sub testme()

Dim myPrecedents
Dim myCell As Range

Set myCell = ActiveSheet.Range("a1")

With myCell
.Formula = "=SUBTOTAL(9,J25:J31)"
MsgBox .Precedents.Address(0, 0)
End With
End Sub

 

[RWN]

Thanks Dave;

That put me on the right track - missed a mental connection to "Precedents".
(was extracting from a "FormulaArray" but KNEW there had to be an easier way-if not 30
different ways.)

Turns out that;

Cells(StRow, StCol).Precedents.Select
For Each cl In Selection
' Do the tax exclusion using "Cells(cl.Row, cl.Column)"
Next

Is what I wanted

Again, thanks.