I have a macro that I use to subtotal lines (I took parts of this from
other macros on this group and elsewhere and don't know who to give
credit to, so thanks whoever you are)

Sub SUBTOTAL()
'- select range and run macro
Dim MyRange As Range
Set MyRange = Selection
ActiveCell.Offset(MyRange.Rows.Count, 0).Formula = _
"=SUBTOTAL(9," & MyRange.Address & ")"
ActiveCell.Offset(MyRange.Rows.Count, 0).Select
' moves to bottom of selection where subtotal was just inserted.
End Sub

As an example I have numbers in B1:B10, I highlight from B1 down to
B10, and hit a button to run the macro. It puts the subtotal in B11.
So far so good.

If I highlight from B10 up to B1, it puts the subtotal in B20.

How do I amend so that I can have it work no matter where I start to
highlight?
Can I do it to subtotal across, and total on the right? And even
better let the macro know if it up/down or across and put the subtotal
in the right place.

Thanks so much.

Hi-This should work. Subtotal is off of the activecell.

Sub SUBTOTAL()
'- select range and run macro
Dim MyRange As Range
Set MyRange = Selection
OffsetValue = MyRange.Rows.Count
If ActiveCell.Row MyRange.Row Then
ActiveCell.Offset(1, 0).Formula = _
"=SUBTOTAL(9," & MyRange.Address & ")"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(MyRange.Rows.Count, 0).Formula = _
"=SUBTOTAL(9," & MyRange.Address & ")"
ActiveCell.Offset(MyRange.Rows.Count, 0).Select
End If
' moves to bottom of selection where subtotal was
just inserted.
End Sub

-----Original Message-----
I have a macro that I use to subtotal lines (I took
parts of this from
other macros on this group and elsewhere and don't know
who to give
credit to, so thanks whoever you are)

Sub SUBTOTAL()
'- select range and run macro
Dim MyRange As Range
Set MyRange = Selection
ActiveCell.Offset(MyRange.Rows.Count, 0).Formula = _
"=SUBTOTAL(9," & MyRange.Address & ")"
ActiveCell.Offset(MyRange.Rows.Count, 0).Select
' moves to bottom of selection where subtotal was
just inserted.
End Sub

As an example I have numbers in B1:B10, I highlight from
B1 down to
B10, and hit a button to run the macro. It puts the
subtotal in B11.
So far so good.

If I highlight from B10 up to B1, it puts the subtotal
in B20.

How do I amend so that I can have it work no matter
where I start to
highlight?
Can I do it to subtotal across, and total on the right?
And even
better let the macro know if it up/down or across and
put the subtotal
in the right place.

Thanks so much.
.

First, I wouldn't use Subtotal as my procedure name. If you record a macro when
you do data|subtotals, you'll see that it's used by excel (and even though excel
will allow you do it, it confuses the heck out of me!):

Option Explicit

Sub mySUBTOTAL()
'- select range and run macro
Dim MyRange As Range
Dim mySubTotalRng As Range

Set MyRange = Selection

If MyRange.Areas.Count < 1 _
Or MyRange.Columns.Count < 1 Then
MsgBox "Please only one area and only one column!"
Exit Sub
End If

With MyRange
Set mySubTotalRng = .Cells(.Cells.Count).Offset(1, 0)
End With
mySubTotalRng.Formula = "=SUBTOTAL(9," & MyRange.Address & ")"
mySubTotalRng.Select

End Sub



Bill wrote:

I have a macro that I use to subtotal lines (I took parts of this from
other macros on this group and elsewhere and don't know who to give
credit to, so thanks whoever you are)

Sub SUBTOTAL()
'- select range and run macro
Dim MyRange As Range
Set MyRange = Selection
ActiveCell.Offset(MyRange.Rows.Count, 0).Formula = _
"=SUBTOTAL(9," & MyRange.Address & ")"
ActiveCell.Offset(MyRange.Rows.Count, 0).Select
' moves to bottom of selection where subtotal was just inserted.
End Sub

As an example I have numbers in B1:B10, I highlight from B1 down to
B10, and hit a button to run the macro. It puts the subtotal in B11.
So far so good.

If I highlight from B10 up to B1, it puts the subtotal in B20.

How do I amend so that I can have it work no matter where I start to
highlight?
Can I do it to subtotal across, and total on the right? And even
better let the macro know if it up/down or across and put the subtotal
in the right place.

Thanks so much.

--

Dave Peterson

The macro stops on the OffsetValue with a Compile Error: Value not set.

I am using Excel 2002.

Thanks

Bill

No Spam out to reply.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I'm looking for something very similar but I want to be able to hit
macro button when the active cell is directly above the data and i
will insert the subtotal formula. Any ideas

--
Message posted from http://www.ExcelForum.com