Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 |
Subtotal row formatting
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 Dan wrote: 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 -- Dave Peterson |
All times are GMT +1. The time now is 05:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com