Find the range of a SubTotal

  • Thread starter Thread starter RWN
  • Start date Start date
R

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).
 
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
 
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.
 
Back
Top