Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a subtotal
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 ! ---- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a subtotal
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 "SharonInGa" wrote: 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 ! ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal To Include Item Description On Subtotal Line | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Sort, Subtotal, Label Subtotal, Insert row | Excel Programming |