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

ActiveCell.RowHeight = 20


"Dan" skrev:

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

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...
--
HTH...

Jim Thomlinson


"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

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,

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

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,

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

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