How to SUM Totals At Bottom of a Column Dynamically - Excel VBA Is Fun!
HTML-код
- Опубликовано: 2 ноя 2024
- 📊 Free Workbooks: www.excelvbais...
🥷Join Excel Ninja Pro: www.excelvbais... Months FREE On Annual Plan Auto Applied)
🥷Excel Ninjas FB Group: www.excelvbais... (Free downloads, Trainings, Live Q&A and more)
In this lesson, we learn to get Totals or Subtotals DYNAMICALLY using VBA to determine the range and to insert totals, maybe even add some extras, like making it BOLD and adding the word "Totals:" beside it. Check it out!
Fantastic Developer Tools:
🔒 Transform Any Excel File Into A Locked EXE: www.excelvbais... (25% off with code ‘25OFF’)
🟡 Create Custom Installers: www.excelvbais...
👋 Business Inquiries, Consulting, Comments, etc: www.excelvbais...
Definitely a Geek! 🤦♂️ Way above what I needed..
Like... in a good way... right? lol
Thanks, AJ. Hope it helps!
Dan
You just saved me. Your video is clear, to the point, and actually really interesting.
I hope you have a great day.
All the best.
Thank you! So glad it helped, Jean!
This was super helpful! Do you have a video explaining how to do this same VBA sum concept except with summing a row horizontally when the last cell in that row is changing as data is added/removed?
Thank you, you explained it very well, and I love the simplicity. I was stuck trying to combine multiple for loops, but this made perfect sense, and it actually worked! Thanks again!
So Glad it helped, Dylan!
Excellent. I'm just getting into this and this was very helpful
Perfect explanation!!! You helped me a lot with this video!!!
I've tried several approaches shown on You Tube, but nothing works to provide dynamic column totals. I wonder if my Professional version Office 2010 is not capable of this VBA? I know MS adds features to Excel each year.
Good one. Thanks 👍
Thanks dude. Your Dropbox files are cool too. I used "SUBTOTAL in Bottom Cell using Excel VBA.xlsm". It's exactly what i needed.
Sweet! Glad it helped!
Thanks for being very detailed in every move you made
Thanks a lot it help me to find the solutions
Very welcome!!
Hey Buddy!! Thanks!! you do not know how difficult were to found a easy solution to the issue with the variable ranges, Thanks a lot.
Very welcome!
Hello, This was awesome. Exactly what i needed. Your work is absolute marvel!!!! Could you please assist on how i can do this for multiple columns without repeating the codes each time. Thanks!
how about i do have a first run for the consolidate for all worksheets into one worksheet. how am i going to sums everythings in my active worksheets
Hi Daniel, what if I have a dynamic filter and I wanted to just get the sum of the filtered dataset? Please advise :) Thanks
Thanks for the help
Thank you very much but if I want to sum 2 columns like this, what will the code be ?
Will you be able to post your code in the description ? It's not showing the entire code.
I'm trying to figure out how to design a model with a message box that requests a positive integer input and then populates a list of all the odd integers that occur before that positive integer entered and also provides the sum of all those negative integers at the bottom of the data set. So for example, if the user enters 8, then there will be a range of negative integers including 1, 3, 5, and 7 and a resulting sum of 16. How can this be achieved?
From Egypt
Thank you very very much
Real nice, thank you, may I ask for an example if there are blank cells in Column B please. Or possibly zeros in the empty cells but the cells with zeros do not have visible text (numbers), maybe something like that if empty cells are not possible. Thank you. Duane
looks good... you might want to try using "with" and "end with" to clean up the code a-bit ;)
You may be right. I generally do that when there are super lots of redundancies. Thanks for your comments, guru dude. Dan
this helped a lot. thanks
That is awesome. Thank you. 👍
So very welcome, Salim!
Well explained thanks
Thanks for the tip. I have another question, using the same method how would you do a subtotal after row change. Example say the first 10 rows in Column A say Mike, there would be a total after that in column B , then the next 7 say Dan this would total, etc.
Thanks Again for the tips
Thankyou^^
need sumif sumifs countif countifs and pivot using vba please help me
Could you please add the vba
The link does’nt work
Thank you sobolev!
Thanks a lot for this great effort Dan!
I guess Im kind of off topic but do anyone know of a good place to stream new movies online?
@Damari Henrik i would suggest Flixzone. You can find it on google =)
@Damari Henrik i would suggest Flixzone. You can find it on google :)
@Rocky Matthias yea, I have been watching on flixzone for since march myself =)
@Rocky Matthias thank you, signed up and it seems like they got a lot of movies there =) Appreciate it!!
congrats, great video
First line of VBA script is coming back with a bug? Highlighted in yellow. I checked your work twice. What I'm trying to do is add specific columns which is more real world at least for my applications. Please show your entire code.
Thank you so mutch
You are so welcome
i am having trouble with a very similar problem but mine is different in that i need to get the average of different size datasets in the same column so just like above the first dataset is so many rows long and you got the total of that now what i need to do i just insert a blank line and enter values for the next dataset and then get the average of that new dataset
EXAMPLE
DATASET1
4
5
5
5
6
6
AVERAGE
?????
DATASET2
1
1
2
3
AVERAGE
????
ANY IDEAS ?
i m facing an error in the first line of code runtime error 9 subscript out of range
Hello can i have the code please
I am getting application defined or object-defined error on using this same code in the first line. Kindly help
Hi Niva,
I wonder if there is text in any of the cells you're trying to add up or some other error. Does the regular SUM function work for the same range? If not, may we see your code, please? Thanks
Dan
@@ExcelVbaIsFun im having the same error message also in the first line , i have 5 numbers to sum together in cells A1 to A5 no text, my sheets name is policka so i exchanged all the sheet1 with it . document type is csv because i had to load the numbers in from visual basic (everytime someone turns on Visual basic the amount and numbers change )
code is =SUM(B2:B15)
It cant work for below codes. Anyone can help?
Sub ltd1()
lastrow = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
ActiveSheet.Range("l" & lastrow + 1) = Application.WorksheetFunction.Sum(ActiveSheet.Range("l2:l" & lastrow).Sum))
End Sub
Try removing the dot sum at the end. Also, I think you have an extra end parenthesis too many at the end. Like this:
Sub ltd1()
lastrow = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
ActiveSheet.Range("l" & lastrow + 1) = Application.WorksheetFunction.Sum( ActiveSheet.Range( "l2:l" & lastrow ) )
End Sub
Thanks
Dan
part of your code is chopped off,..
dear, you must clear your concepts first before uploading the videos
this is confusing. seems you would know how to do it before making the video?
Thanks, helped a lot.
Thanks for the tip.