G
Guest
Is there a way to automatically insert a blank row below each subtotal in worksheet?
Thanks for any help you can provide
Thanks for any help you can provide
K
Ken Wright
Yes this can be done quite easily with VBA, but if this is simply to visually
separate the blocks of data, have you considered maybe just increasing the row
heights of the rows containing the subtotals, and then setting the Vertical
alignment to 'Top'.
Assuming your data is all constants, and that the only formulas in the column in
question are the Subtotal formulas, then select the range concerned, do Edit /
Go To / Special / Formulas. Now with those cells selected, do Format / Row /
Height, and double whatever is in there, and then do Format / cells / Alignment
Tab / vertical - set to 'Top'.
This will not work if the data you are subtotalling is the result of formulas as
well.
separate the blocks of data, have you considered maybe just increasing the row
heights of the rows containing the subtotals, and then setting the Vertical
alignment to 'Top'.
Assuming your data is all constants, and that the only formulas in the column in
question are the Subtotal formulas, then select the range concerned, do Edit /
Go To / Special / Formulas. Now with those cells selected, do Format / Row /
Height, and double whatever is in there, and then do Format / cells / Alignment
Tab / vertical - set to 'Top'.
This will not work if the data you are subtotalling is the result of formulas as
well.
G
Guest
Ken
Thanks. Increasing the row height and Top align is not an option as the file is to be exported as text file. So the format will be lost. Can you please explain how this can be done in VBA
----- Ken Wright wrote: ----
Yes this can be done quite easily with VBA, but if this is simply to visuall
separate the blocks of data, have you considered maybe just increasing the ro
heights of the rows containing the subtotals, and then setting the Vertica
alignment to 'Top'
Assuming your data is all constants, and that the only formulas in the column i
question are the Subtotal formulas, then select the range concerned, do Edit
Go To / Special / Formulas. Now with those cells selected, do Format / Row
Height, and double whatever is in there, and then do Format / cells / Alignmen
Tab / vertical - set to 'Top'
This will not work if the data you are subtotalling is the result of formulas a
well
Thanks. Increasing the row height and Top align is not an option as the file is to be exported as text file. So the format will be lost. Can you please explain how this can be done in VBA
----- Ken Wright wrote: ----
Yes this can be done quite easily with VBA, but if this is simply to visuall
separate the blocks of data, have you considered maybe just increasing the ro
heights of the rows containing the subtotals, and then setting the Vertica
alignment to 'Top'
Assuming your data is all constants, and that the only formulas in the column i
question are the Subtotal formulas, then select the range concerned, do Edit
Go To / Special / Formulas. Now with those cells selected, do Format / Row
Height, and double whatever is in there, and then do Format / cells / Alignmen
Tab / vertical - set to 'Top'
This will not work if the data you are subtotalling is the result of formulas a
well
K
Ken Wright
OK, how do you delineate your subtotal lines. Is there a word like Subtotal in
say Col A for each subtotal, or how would you know which rows to insert a row
underneath. Are they all identical sized ranges by any chance?
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------
explain how this can be done in VBA?<snip>
say Col A for each subtotal, or how would you know which rows to insert a row
underneath. Are they all identical sized ranges by any chance?
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------
is to be exported as text file. So the format will be lost. Can you pleaseDevi said:
explain how this can be done in VBA?<snip>
G
Guest
Ken
Below is an example to show the subtotal with "Count" as the common string in all rows where I need a blank row below
Col a Col b Col
1 000002
2 000002
000002 Count 2
3 000006
4 000006
6 000006
000006 Count 3
B 000002
B 000002
000002 Count 2
Cl 1 Cl 2
Cl 2 Count 1
Cl 1 000003
Cl 1 000003
Cl 1 000003
Cl 1 00000
000003 Count 4
D 00000
D 00000
D 00000
D 00000
000004 Count 4
E 00000
E 00000
E 00000
E 00000
000005 Count 4 *
Below is an example to show the subtotal with "Count" as the common string in all rows where I need a blank row below
Col a Col b Col
1 000002
2 000002
000002 Count 2
3 000006
4 000006
6 000006
000006 Count 3
B 000002
B 000002
000002 Count 2
Cl 1 Cl 2
Cl 2 Count 1
Cl 1 000003
Cl 1 000003
Cl 1 000003
Cl 1 00000
000003 Count 4
D 00000
D 00000
D 00000
D 00000
000004 Count 4
E 00000
E 00000
E 00000
E 00000
000005 Count 4 *