Dave,

Again, please pardon my ignorance, but how do I get rid of the rows that I
don't want before apply the subtotaling (if I'm not going to use AutoFilter)?
Should I put a loop in my macro that checks all the values in the row that
was being filtered for zero and delete those rows before subtotaling?

Thanks for your help!

Dan

"Dave Peterson" wrote:

First, I wouldn't use data|subtotals and Data|autofilter on the same data.

The =subtotal() function inserted by data|subtotals will ignore the rows hidden
by the data|autofilter.

I know that I've removed data|filter from a few workbooks to make them easier to
understand.

But maybe you could try to show all the rows, apply the data|subtotals, and then
reapply the filtering.

(I really wouldn't do this--the confusion about subtotals is enough to stop me!)



Dan wrote:

Dave,

I did what you said and clicked on the little "2" button. I have filtering
turned on, and I had previously done a custom selection on one column for
values greater than zero. When I click on the little "2" button, some of the
rows that were hidden by the filter reappear, even though the value in that
column is zero. It's not all the rows that were hidden by the filter, just
some of them. Any ideas as to how to get the filtered-out rows to stay hidden
when I want the subtotal rows?

Dan

"Dave Peterson" wrote:

Turn off the macro recorder
Apply the subtotals (if you haven't already)
Notice the little boxes with numbers in them on the left hand side.
Click on the number to the far right (the highest number).

Turn on the macro recorder
click on the little box with 2 in it (or whatever you want to see)

Turn off the macro recorder and inspect your code.

That's how I got that line in my code.

Dan wrote:

Dave,

Pardon my ignorance, but I don't know how to hide the details from the menus
while I'm recording a macro. Thanks for your help!

Dan

"Dave Peterson" wrote:

Record a macro when you hide those details. See what gets recorded.

Dan wrote:

Dave,

Thank you! Your code was helpful. I inserted it into my macro, and it worked
fine with one exception. It didn't hide the detail rows. They plus the total
rows got formatted with the same row height and color. Is there something
else that needs to be done with the following line:

.Outline.ShowLevels rowlevels:=2

to hide the details?

Dan

"Dave Peterson" wrote:

After you apply data|subtotals, you can hide all the details.

Then you can select the first column (avoid the headers).
Edit|goto|special|visible cells only
and then adjust the row height
format|row|height|20

If you record a macro, you'll see some useable code.

I modified my recorded macro to get this:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myVRng As Range

'do your subtotals here

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
'hide the details
.Outline.ShowLevels rowlevels:=2

Set myVRng = Nothing
On Error Resume Next
Set myVRng = myRng.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If myVRng Is Nothing Then
'something went wrong
Else
With myVRng.EntireRow
.RowHeight = 20
'change the color, too???
.Font.ColorIndex = 3
End With
End If
End With

End Sub

Dan wrote:

I have a macro that includes subtotaling. Is there a way to specify
formatting of the subtotal rows that are automatically inserted, without
having to do it manually after the macro finishes? For example, I'd like to
specify the row height for all of the subtotal rows.

Dan

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson