Hi there,
I've recently been using autosum feature quite a bit and was wondering how that could be put into a macro.
The shortcut is to press Alt and += button.
I recorded this and tried it but the macro did not have a dynamic range in terms of how many numbers it added. it only copied how many numbers were in the range when I recorded it (eg. 3) and only sums up 3 numbers above now.
The AutoSum is a human shortcut and is meant to work that way. Any attempt to use it in VBA would also require the use of "selecting" and "sendkeys"...neither of which is efficient. Since macros work fastest without utilizing the sheet directly, best to do the work all in VBA.
There are many ways, all affected by your sheet design. If your column of values stands alone with empty cells all around, this will get you a sum in one line of code:
The red part could be substituted with a cell reference to indicate a cell you know will always be a part, like the first cell is always M1...
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I understood the question a bit different.
Making an autoSum in VBAPlease Login or Register to view this content.
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
wow that works great! Thanks very much. And thanks to all who tried to help as well.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Re: How to program the AutoSUBTOTAL feature in Excel
The code provided by rwgrietveld made me register for this forum, as I was looking for a way to do a autoSUBTOTAL. I know that Alt = will provide the sum function to adjacent cells, but I wanted a similar keyboard shortcut for subtotal. I modified the code to:
Assigned Ctrl T and now I am good to go.Please Login or Register to view this content.
Thanks!
-new member Laura aka flatflo
Thanks for your added variation, flatflo. And welcome to the forum.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks