Subtotal row formatting

[Guest]

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

 

[Guest]

ActiveCell.RowHeight = 20


"Dan" skrev:

 

[Guest]

Nope... What you can do is to add an auto format, but that will not change
the row height. it will however colour and border he subtotal rows different
from the data. Just as an aside do not Auto format on data ranges over 10,000
rows as it is a painfully slow task. In that case us a pivot table with
formatting which is infinitly faster...

 

[Dave Peterson]

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

 

[Guest]

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]

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

 

[Guest]

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]

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.

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

 

[Guest]

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]

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!)

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

 

[Guest]

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]

That seems like a reasonable approach.

Or you could apply the data|autofilter, filter to show the rows you don't want,
then delete those visible rows.

It usually makes things lots easier if you loop from the bottom to the top.

Dim LastRow as long
dim FirstRow as long
dim iRow as long

with worksheets("Sheet1")
firstrow = 2 'headers in row 1???
lastrow = .cells(.rows.count,"A").end(xlup).row

for irow = lastrow to firstrow
if .cells(irow,"A").value = 0 then
.rows(irow).delete
end if
next irow
end with

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