finding a subtotal

[Guest]

How would I use VBA to find a subtotal in a report.
Subtotals are based on the number of customers that we have and the subtotal
row position changes each time the report is run.

---- i do NOT want to use a pivot table ! ----

 

[Guest]

No pivot tables. You take all of the fun out of it...

Here is some code for finding subtotals. It looks in column B, but you can
change that pretty easily. It looks at the active sheet, but you can change
that pretty easily. It also selects the entire row where the subtotal was
found, but that can be changed pretty easily...

Public Sub FindSubTotals()
Dim wksCurrent As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String
Dim rngSubTotal As Range

Set wksCurrent = ActiveSheet
Set rngToSearch = wksCurrent.Range("B1").EntireColumn
Set rngFound = rngToSearch.Find("=SUBTOTAL", , xlFormulas, xlPart, , ,
True)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Set rngSubTotal = rngFound.EntireRow
Do
Set rngFound = rngToSearch.FindNext(rngFound)
Set rngSubTotal = Union(rngSubTotal, rngFound.EntireRow)
Loop Until rngFound.Address = strFirstAddress
rngSubTotal.Select
End If

End Sub

HTH