G
GaiGauci
I have posted this again to hope that someone will give me further assistance
- I "ticked" the last post because it did work and well, but I realised what
I actually need is slightly different.
I asked for some coding that would search for each blank cell in a column
and subtotal below it until the next blank cell. Stefi gave me this:
Sub stotal()
BUT... what I realised when I put it into play was that what I really need
was it to subtotal when both S and T columns are both empty at the same time,
because there are odd cells that are empty but are not a sub total row. It is
only those rows that have both S and T columns (the total columns) empty that
are the subtotal columns.
I was guessing something like....
Range("S1").End(xlDown).Activate
Range("S" & ActiveCell.Row - 1).Activate
Do While ActiveCell.Row <> Range("S:T" & Rows.Count).End(xlUp).Row + 1
stotstart = ActiveCell.Row + 1
stotend = Range("S" & ActiveCell.Row + 1).End(xlDown).Row
with selection.font
.font = 11
.bold = true
ActiveCell.Formula = "=subtotal(9,S" & stotstart & ":S" & stotend & ")"
Range("S" & stotend + 1).Activate
Nextstotstart = ActiveCell.Row + 1, ActiveCell.column +1
Nextstotend = Range("T" & ActiveCell.Row + 1).End(xlDown).Row
ActiveCell.Formula = "=subtotal(9,T" & nextstotstart & ":T" &
nextstotend & ")"
Range("S" & stotend + 1).Activate
Loop
What are your thoughts??? I really don't understand coding as well as I
would like so I am just having a stab in the dark.
Cheers
Gai
- I "ticked" the last post because it did work and well, but I realised what
I actually need is slightly different.
I asked for some coding that would search for each blank cell in a column
and subtotal below it until the next blank cell. Stefi gave me this:
Sub stotal()
Range("S1").End(xlDown).Activate
Range("S" & ActiveCell.Row - 1).Activate
Do While ActiveCell.Row <> Range("S" & Rows.Count).End(xlUp).Row + 1
stotstart = ActiveCell.Row + 1
stotend = Range("S" & ActiveCell.Row + 1).End(xlDown).Row
ActiveCell.Formula = "=SUBTOTAL(9,S" & stotstart & ":S" & stotend &
")"
Range("S" & stotend + 1).Activate
Loop
End Sub
BUT... what I realised when I put it into play was that what I really need
was it to subtotal when both S and T columns are both empty at the same time,
because there are odd cells that are empty but are not a sub total row. It is
only those rows that have both S and T columns (the total columns) empty that
are the subtotal columns.
I was guessing something like....
Range("S1").End(xlDown).Activate
Range("S" & ActiveCell.Row - 1).Activate
Do While ActiveCell.Row <> Range("S:T" & Rows.Count).End(xlUp).Row + 1
stotstart = ActiveCell.Row + 1
stotend = Range("S" & ActiveCell.Row + 1).End(xlDown).Row
with selection.font
.font = 11
.bold = true
ActiveCell.Formula = "=subtotal(9,S" & stotstart & ":S" & stotend & ")"
Range("S" & stotend + 1).Activate
Nextstotstart = ActiveCell.Row + 1, ActiveCell.column +1
Nextstotend = Range("T" & ActiveCell.Row + 1).End(xlDown).Row
ActiveCell.Formula = "=subtotal(9,T" & nextstotstart & ":T" &
nextstotend & ")"
Range("S" & stotend + 1).Activate
Loop
What are your thoughts??? I really don't understand coding as well as I
would like so I am just having a stab in the dark.
Cheers
Gai