L
Laurisa.Prince
Hi, I'm hoping someone can help me. I'm working with Excel97.
Using the subtotal function, is there a way to find the number of rows
that now exist after the subtotal has done its' magic?
Problems/reasons below...
I'm seeking to automate creating subtotals on a spreadsheet. Here is a
snippet of the code I'm working with:
' Clear all subtotals
Cells.Select
Selection.RemoveSubtotal
' Select range of data
Range("labor_test").Select
' Do first subtotal
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(8, 9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'(THIS IS WHERE I HAVE THE PROBLEM)
' Do the next subtotal
Selection.Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(8, 9), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
My problem is that doing this via VBA, it seems to not be able to
figure out what the range is supposed to be for the second subtotal -
it seems to think the range is short by the number of subtotal rows
and it misbehaves and creates extra subtotal lines. When I use the
pulldown on the toolbar under Data, Subtotals, it keeps track of the
selection range and has the right number of rows highlighted when I go
to repeat the proceedure. I did use the macro recorder, but what you
see above is exactly what it gave me.
Thanks!!!
Using the subtotal function, is there a way to find the number of rows
that now exist after the subtotal has done its' magic?
Problems/reasons below...
I'm seeking to automate creating subtotals on a spreadsheet. Here is a
snippet of the code I'm working with:
' Clear all subtotals
Cells.Select
Selection.RemoveSubtotal
' Select range of data
Range("labor_test").Select
' Do first subtotal
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(8, 9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'(THIS IS WHERE I HAVE THE PROBLEM)
' Do the next subtotal
Selection.Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(8, 9), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
My problem is that doing this via VBA, it seems to not be able to
figure out what the range is supposed to be for the second subtotal -
it seems to think the range is short by the number of subtotal rows
and it misbehaves and creates extra subtotal lines. When I use the
pulldown on the toolbar under Data, Subtotals, it keeps track of the
selection range and has the right number of rows highlighted when I go
to repeat the proceedure. I did use the macro recorder, but what you
see above is exactly what it gave me.
Thanks!!!