Macro with Autosum

Posted by jthompson on December 24, 2001 11:15 AM

I need a formula in a macro that will sub-total all the cells above it. When I record this macro it always does it in relative terms. The rows will change so I need it just to perform like autosum would finding all filled cells that are above the cell. I have tried to record this with the relative button pushed down and with it up and it records the same way each time. Any help would be appreciated.
Thanks

Posted by Gary Bailey on December 24, 2001 12:16 PM

Have you tried recording the macro using Ctrl+Shift+up to select the range? That should work in Excel 2000 or later. The code below should do it also

Dim strAddress As String

strAddress = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)).Address(False, False)

ActiveCell.Formula = "=sum(" & strAddress & ")"

Gary

Posted by Ivan F Moala on December 24, 2001 3:09 PM

Here is one way;
Note: You will have to have the active cell
below the column to autosum

Sub AutoSum()
CommandBars.FindControl(ID:=226).Execute
Application.SendKeys "~"
End Sub

Ivan

Posted by jthompson on December 27, 2001 7:53 AM

Gary andr Ivon

Posted by jthompson on December 27, 2001 7:55 AM

Gary andr Ivon

When I use the one starting with Sub AutoSum() I get an Expected Statement or End of Statement at the beginning.

When I use The one staring with Dim I get expected end of statement on the last line.

When I use the strl shift up it still does it relative.

Any ideas would be appreciated.

Posted by Jack in UK on December 27, 2001 2:34 PM

Re: Gary andr Ivon

Hi --
I am amased at Ivans code works fine, multi select and i will add that to my toolkit poss the best 3 line cide ive seen.

Ok autosum can go up or rioght, but thats excel for you, but all i can suggest is custom function UDF, but thats pointless as has own already

All i can suggest is highlight the range and hit autosum button thus answer is one below.

HTH
PS Ivan.. cheers mate ill use that as i like it----- a lot, will develope in UDF poss, ill post if i do, have idea for it, cheers again