Excel - Crazy Macro Recorder Shenanigans - Episode 1885
2,947 views · Published 1 June 2014 · 6:50 · Indexed 24 October 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial: Excel Macro Recorder Shenanigans: Recording AutoSum and Sorting - Crazy Workarounds! Welcome back to the MrExcel netcast! In this episode, we're diving into the world of Excel macros and the crazy shenanigans we've had to use to make the Macro Recorder work. As always, this podcast is sponsored by MrExcel.com, your go-to resource for learning all things Excel. Today's topic is one that I'm sure many of you can relate to - the love-hate relationship with the Macro Recorder. We all know that it can be a powerful tool for automating tasks, but sometimes it just doesn't record the way we want it to. So, the question is, what crazy shenanigans have you had to use to get the Macro Recorder to work? One of my biggest pet peeves is the Macro Recorder's inability to record the intent of the AutoSum function. We all know that AutoSum is a handy tool for quickly adding up a column of numbers, but unfortunately, the Macro Recorder doesn't always capture it correctly. For example, if we record a macro to add a total at the bottom of column D, it will only include the last 10 cells in the formula. This is where the crazy shenanigans come in. But fear not, my friends! Thanks to one of my seminar students, I've discovered a workaround that actually works. By using the dollar sign in the formula while the Macro Recorder is running, we can get a total that works with any number of rows. It may seem crazy to include that dollar sign, but trust me, it's worth it. Now, I have a challenge for you. Can you figure out a way to sort a column and then subtotal at each change in that column using the SUM function? It sounds simple enough, but the Macro Recorder just can't seem to handle it. Even when we try to convert the data into a table, it still fails to record the correct code. So, I'm turning to you, my fellow Excel enthusiasts, for help. If you have a workaround that successfully records a sort and subtotal, please share it in the comments below. I know many of you are just starting to dip your toes into the world of VBA and the Macro Recorder can be a great tool for learning. But it can also be frustrating when it doesn't record the way we want it to. So, let's work together to find those crazy shenanigans that make the Macro Recorder work for us. Thanks for tuning in and we'll see you next time for another netcast from MrExcel. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Discussion on Macro Recorder Shenanigans (00:23) Pet Peeve: Macro Recorder Cannot Record AutoSum Intent (00:33) Example of AutoSum Macro Recording (01:02) Proper Way to Record AutoSum Macro (01:44) Challenge for Listeners: Sort and Subtotal Macro (02:40) Attempt 1: Sort and Subtotal Macro Recording (03:05) Attempt 2: Sort and Subtotal Macro Recording (04:06) Attempt 3: Sort and Subtotal Macro Recording (05:31) Discussion on Failed Attempts and Request for Workarounds (06:00) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: AutoSum Convert to range Excel table Learn Excel Macro Recorder Relative references Sorting Subtotal function Total formula VBA workaround Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153710/ Have you ever discovered crazy steps that will actually cause the macro recorder to work? In today's episode, I share my way to have the Macro Recorder successfully record the AutoSum. But, I am looking for a way to use the macro recorder to record a Sort.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920