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

Watch on YouTube

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