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.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel for MrExcel podcast episode 1884.
What Crazy Shenanigans HAVE YOU used to make the Macro Recorder work?
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'm considering my love hat relationship with the Macro Recorder.
Today the title is, What Crazy Shenanigans HAVE YOU had to do to get the Macro Recorder to work?
And one of my big pet peeves is the Macro Recorder cannot record the intent of the AutoSum.
All right so, we're getting data from the system every single day we have a different number of rows, we need to be able record a Macro that's going to put a grand total at the bottom of column D.
Now, if we just do a regular Macro.
I'm gonna call this AutoSumTry1 and we'll store here in This workbook, click OK.
All right obviously, we have to turn on a relative references control down arrow to go to the bottom of the data down arrow one more time right arrow 1, 2, 3 times.
If I just simply hit the AutoSum it will put a total in control+enter and stop recording, but unfortunately, that does not get recorded in my opinion correctly by the Macro Recorder.
So, here on the next date now you see we have more records and I will do Alt+F8 and run that Macro it puts the total in the right place that's because I had relative turned on when I before use control down arrow, but the formula only included the last 10 cells because that's how many cells that were on day one.
Right horrible, horrible, horrible and thanks to one of my seminar students in York, Pennsylvania.
They taught me that's the proper way todo this is to record the Macro, We'll call it AutoSumTry2, click OK.
Again make sure that relative is on control down arrow, down arrow one more time right, right, right, =SUM(, and this is really crazy you have to do D dollar sign 2 colon D11.
No one would ever type that dollar sign there it's completely unnecessary, but when you use that while the Macro Recorder is running, then you will get a total that works here with ten rows and remarkably you'll get a total that works with more rows.
So, Alt F8 and AutoSumTry2, Run, the total appears in the correct spot and it always goes back up to row 2.
All right so, that's one of my crazy shenanigans I have to do to get the Macro recorder to work.
Now, I have a challenge for you.
Here is a data set simple little thing again different number of rows come in every day.
I wanna be able to sort by column A and then subtotal at each change in column A use the SUM function on column G sounds really, really simple. Right?
All right, so we record a Macro we'll call it SortSubtotalTry1, click OK.
We're already assuming that we're in cell A1.
So, on the data tab A to Z, it's now sorted.
I'll select all so, control+asterisk and then Subtotal and each change and heading one add it to the amount, click OK.
Beautiful, stop recording and then we'll come here to the big data set and we'll try that Alt F8 and SortingSubtotalTry1, click Run and we get at an error and for some reason this is just a flat out bug in the Macro recorder they decided that the heading was going to be the active cell and then one row above that that wasn't the case at all and if we could somehow, get past that it gets even worse because here they hard code the fact that we are sorting A1 to G10.
Hey, I have relative turned on put dot current region there and the thing would have worked.
All right, let's try selecting the whole range first.
So, Developer, Record Macro I will call it SortSubtotalTry2.
This time I'll select the whole range first control+shift+asterisk.
On the Data tab see the active cell is still column A, let's see you can try this will just sort by Heading 1, A to Z, click OK.
Still the whole range selected, Subtotals, At each change in, Heading 1.
Looks good, stop recording, come here big to try it again Alt F8, SortingSubtotalTry2, Run.
Again same problem with it looking one row above, but even worse up here it is hard coding it's only sorting rows 1 to 10.
What the heck.
How can the Macro Recorder not Record a sort.
Now, you know then I thought well let's try this let's make it into a Table, Record Macro, SortSubtotalTry3, click OK.
First thing control+T to make it into a Table, click OK and then we use this little heading here to sort, sort A to Z then we'll come out to Data, Subtotals, grayed out you can't add subtotals to a table no problem we'll come here convert to range and now, I'll do the Subtotals, click OK and stop recording, but when we go look at that code Alt+F8, Edit, back in my head against the wall when I did control+T they hard-coded the shape of today's range instead of the whole range.
It does successfully sort without specifying the hard-coded range, but I'm shot down there with a hard-coded range and right here before the Subtotal I did convert to range Macro Recorder completely ignored that failed to use the dot on list.
All right so, hey, I'm looking for your help.
If you have a workaround, where you've actually had the Macro Recorder, successfully record something like this sort and then subtotal.
Please in the YouTube comments let me know what your crazy workaround is or anything if you've found some crazy workaround, to allow the Macro Recorder to actually work.
I have friends who are not familiar with VBA they're just trying to dip their toe in the water and record a Macro that's actually useful from one day to the next and I can't believe that they can't get this sort to work correctly.
Well hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel for MrExcel podcast episode 1884.
What Crazy Shenanigans HAVE YOU used to make the Macro Recorder work?
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'm considering my love hat relationship with the Macro Recorder.
Today the title is, What Crazy Shenanigans HAVE YOU had to do to get the Macro Recorder to work?
And one of my big pet peeves is the Macro Recorder cannot record the intent of the AutoSum.
All right so, we're getting data from the system every single day we have a different number of rows, we need to be able record a Macro that's going to put a grand total at the bottom of column D.
Now, if we just do a regular Macro.
I'm gonna call this AutoSumTry1 and we'll store here in This workbook, click OK.
All right obviously, we have to turn on a relative references control down arrow to go to the bottom of the data down arrow one more time right arrow 1, 2, 3 times.
If I just simply hit the AutoSum it will put a total in control+enter and stop recording, but unfortunately, that does not get recorded in my opinion correctly by the Macro Recorder.
So, here on the next date now you see we have more records and I will do Alt+F8 and run that Macro it puts the total in the right place that's because I had relative turned on when I before use control down arrow, but the formula only included the last 10 cells because that's how many cells that were on day one.
Right horrible, horrible, horrible and thanks to one of my seminar students in York, Pennsylvania.
They taught me that's the proper way todo this is to record the Macro, We'll call it AutoSumTry2, click OK.
Again make sure that relative is on control down arrow, down arrow one more time right, right, right, =SUM(, and this is really crazy you have to do D dollar sign 2 colon D11.
No one would ever type that dollar sign there it's completely unnecessary, but when you use that while the Macro Recorder is running, then you will get a total that works here with ten rows and remarkably you'll get a total that works with more rows.
So, Alt F8 and AutoSumTry2, Run, the total appears in the correct spot and it always goes back up to row 2.
All right so, that's one of my crazy shenanigans I have to do to get the Macro recorder to work.
Now, I have a challenge for you.
Here is a data set simple little thing again different number of rows come in every day.
I wanna be able to sort by column A and then subtotal at each change in column A use the SUM function on column G sounds really, really simple. Right?
All right, so we record a Macro we'll call it SortSubtotalTry1, click OK.
We're already assuming that we're in cell A1.
So, on the data tab A to Z, it's now sorted.
I'll select all so, control+asterisk and then Subtotal and each change and heading one add it to the amount, click OK.
Beautiful, stop recording and then we'll come here to the big data set and we'll try that Alt F8 and SortingSubtotalTry1, click Run and we get at an error and for some reason this is just a flat out bug in the Macro recorder they decided that the heading was going to be the active cell and then one row above that that wasn't the case at all and if we could somehow, get past that it gets even worse because here they hard code the fact that we are sorting A1 to G10.
Hey, I have relative turned on put dot current region there and the thing would have worked.
All right, let's try selecting the whole range first.
So, Developer, Record Macro I will call it SortSubtotalTry2.
This time I'll select the whole range first control+shift+asterisk.
On the Data tab see the active cell is still column A, let's see you can try this will just sort by Heading 1, A to Z, click OK.
Still the whole range selected, Subtotals, At each change in, Heading 1.
Looks good, stop recording, come here big to try it again Alt F8, SortingSubtotalTry2, Run.
Again same problem with it looking one row above, but even worse up here it is hard coding it's only sorting rows 1 to 10.
What the heck.
How can the Macro Recorder not Record a sort.
Now, you know then I thought well let's try this let's make it into a Table, Record Macro, SortSubtotalTry3, click OK.
First thing control+T to make it into a Table, click OK and then we use this little heading here to sort, sort A to Z then we'll come out to Data, Subtotals, grayed out you can't add subtotals to a table no problem we'll come here convert to range and now, I'll do the Subtotals, click OK and stop recording, but when we go look at that code Alt+F8, Edit, back in my head against the wall when I did control+T they hard-coded the shape of today's range instead of the whole range.
It does successfully sort without specifying the hard-coded range, but I'm shot down there with a hard-coded range and right here before the Subtotal I did convert to range Macro Recorder completely ignored that failed to use the dot on list.
All right so, hey, I'm looking for your help.
If you have a workaround, where you've actually had the Macro Recorder, successfully record something like this sort and then subtotal.
Please in the YouTube comments let me know what your crazy workaround is or anything if you've found some crazy workaround, to allow the Macro Recorder to actually work.
I have friends who are not familiar with VBA they're just trying to dip their toe in the water and record a Macro that's actually useful from one day to the next and I can't believe that they can't get this sort to work correctly.
Well hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
