I'm an experienced Excel user but new to macros. I'd like to have a
macro that creates a subtotal at the top of a column of numbers rather
than at the bottom, where Excel normally wants to put it. I've given
it a shot with the following code, but am stumped. Any advice?

Dim MyRange As Range
Start_Row = ActiveCell.Row
Start_Col = ActiveCell.Column
Row_Counter = 0
End_Row = 0

For Row_Counter = (Start_Row + 1) To 65536
If Cells(Row_Counter, Start_Col) = "" Then
End_Row = Row_Counter
Exit For
End If
Next Row_Counter

Set MyRange = Range(Cells(Start_Row + 1, Start_Col), Cells(End_Row - 1,
Start_Col))
ActiveCell.Offset(0, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,[MyRange])"

Instead of code have you considered using a pivot table? One of the
formatting options is to place the aggregations (subtotals in this case) at
the top of the listing.
--
HTH...

Jim Thomlinson


" wrote:

I'm an experienced Excel user but new to macros. I'd like to have a
macro that creates a subtotal at the top of a column of numbers rather
than at the bottom, where Excel normally wants to put it. I've given
it a shot with the following code, but am stumped. Any advice?

Dim MyRange As Range
Start_Row = ActiveCell.Row
Start_Col = ActiveCell.Column
Row_Counter = 0
End_Row = 0

For Row_Counter = (Start_Row + 1) To 65536
If Cells(Row_Counter, Start_Col) = "" Then
End_Row = Row_Counter
Exit For
End If
Next Row_Counter

Set MyRange = Range(Cells(Start_Row + 1, Start_Col), Cells(End_Row - 1,
Start_Col))
ActiveCell.Offset(0, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,[MyRange])"