Today I want to show you how you can eliminate boring and repetitive tasks in Microsoft Excel using something called macros and VBA, or Visual Basic for applications. With macros you can automate those everyday tasks in Excel, and by learning macros, you’ll look like a wizard in Excel. We’re going to start with how to record just a simple macro. Then I’ll show you how you could run it, how you can test it, how you could run your macro in any spreadsheet at all. Then we’ll also touch on some concepts like using if statements and setting up loops, and we’ll look at many different types of loops. If you’d like to follow along today, I’ve included a sample spreadsheet right up above and down below in the description. All right, let’s check this out. If you look at the definition of macro, it’s a single instruction that expands to a set of instructions to perform a particular task. But what does that actually mean? The best way to understand is to use an example. Here I am in Microsoft Excel and I have orders for the Kevin Cookie Company. When an order is unfulfilled, basically meaning that we haven’t shipped it out, I want to format it differently so it stands out a little bit more and I don’t miss it. And once we fulfill the order, I’ll change the formatting back. To make sure I don’t miss it, here I’ll highlight this row and then I’ll go up to the home ribbon and here I’ll set it to bold. I’ll set it to italics. Here I’ll apply an underline and let me apply an orange fill. If you were counting as I ran through all of those steps, that was five separate clicks. Every time in order comes in, I have to run through those exact same steps. Now of course I can select this cell. I could select the format painter and then I could paste the formatting in, but even that still takes 3 clicks. So either way I’m still doing more work than I should. In this example, I would much rather simply press a shortcut key or click on a button and then have Excel automate all of those different steps and that would help me save all of those different clicks. Now this is just an arbitrary example, but if you find yourself doing the same steps again and again in Excel and you want to make those steps faster, well that’s an excellent candidate to turn into a macro. We’re going to use something called a macro recorder to turn all of these steps, or all of these clicks into computer code or VBA, and that’s a programming language. Once we record our macro, you can launch it or run it in many different ways. You can press a shortcut key. You could press a button. You could also access it through a menu. The main summary is if you’re annoyed by running repetitive tasks again and again, you can automate all of that using macros and this was just a very simple example, but you can also use macros for far more complex examples. Let’s start with how to record a basic macro that will do all of these steps and formatting for us. First off, let’s go up to the top tabs and let’s click on the one that says view. Over on the far right hand side, you’ll see an option for macros. If we click on this drop down right here, you’ll see the option to record a macro. Let’s click on that. This opens up the record macro prompt and right up on top it currently has the name Macro1. Now of course that’s not descriptive. We can do better than that. Here I’ll type in HighlightNewOrders. One thing to note with the name, you cannot include any spaces in the name, so here I’ve capitalized the 1st letter of each word. You could also include underscores to separate the different words. For the macro name you want to make sure that you choose something descriptive, so when you go back later on and you look at your macros, you’ll know what it does. Down below you can also specify a shortcut key combination to run your macro, so you could simply press keys on your keyboard and your macro will run and this is optional. You don’t have to select a shortcut key if you don’t want to. Here I see control + and then I could type in a letter. You could also press the shift key together with a letter and that way you can make your shortcut key a little bit more complex. One note on shortcut keys, whatever combination you select here will override any shortcut keys in Excel, so you want to make sure you don’t conflict with any Excel shortcut keys that you might already be using. If you’re curious what all the different existing shortcut keys are in Excel, I’ve included a link in the description down below that lists all of them out. Next we can specify where we want to store this macro and currently this macro will only be connected to this workbook. Later on, we’ll look at the personal macro workbook, which allows you to access your macro from whatever spreadsheet you happen to have open. Lastly, I can also type in a description down below. Here I’ll type in a quick description that describes what this macro will do. Once again, this is helpful if let’s say you have a lot of macros and maybe you don’t remember what each macro does, or it’s especially useful if you share this workbook with someone else and someone else is going to run your macros. I’m all done now filling out these details, so I’ll click on OK. My macro is now recording and now I could run through all of the difference steps. Here I’ll go down and here I have my first unfulfilled order. I’ll highlight the row. Once again, that’s one click. I’ll go up to the ribbon. I’ll click into home and now I’ll apply my formatting. I’ll set it bold, italics, underline, and I’ll also apply the fill. Now that I’ve run through all of my different steps, once again, I’ll go up to view on the top tabs. I’ll go over to macros and here I can click on stop recording. Now that we’ve converted these five steps into just one click, let’s take a look at how we can run our macro, and there are a few different ways that you can run macros. First, let’s remove the formatting on this row. Here I’ll make sure that this row is highlighted. I’ll click on the home tab, and then over here I’ll select the option that says clear and then I’ll select clear formats. Now that we’ve cleared the formatting, let’s run the macro that we recorded, and once again there are multiple ways to run a macro. First off, let’s click once again up on the view tab up on top and all the way over on the right hand side where we see macros, click on the drop down and then select view macros. Here we see the macro that we recorded. With this highlighted, I can click on run or I can double click on this to run it. I’ll click on run and there you see it just ran the macro and applied all of that formatting. Once again I’ll remove the formatting and this time I want to use the shortcut key. If you remember from earlier we set the shortcut key to Control + Shift + N and when I press that that two automatically applies all of that formatting. Once again, I’ll clear out the formatting on this sheet and yet another way I can run this macro is by clicking on an image or a button. Here I have the Kevin Cookie Company logo on top and when I select that I can right click and right here there’s the option to assign a macro. When I click on that, that once again opens up the macro prompt and here I see my highlight new orders macro. I’ll click on OK and when I click on this logo now look at that, that also applies the formatting. Let’s once again remove the formatting and I’ll show you yet another way that you could run your macro and this time we’re going to use the quick access toolbar, and right now I don’t have it enabled. Here on the ribbon, I’ll right click and I’ll select show quick access toolbar. I have my quick access toolbar down below, but you may also have it up above. I’ll right click on the quick access toolbar and here there’s the option to customize. Click on that. This opens up the quick access toolbar options and right here it’s currently set to popular commands. When I click on this dropdown, I see the option for macros and here I see the macro that we just created called HighlightNewOrders. Here I’ll add it to my quick access toolbar. I can click on modify down below and I can choose a different icon for this macro and I’m highlighting these cells in orange so I think it would be appropriate to choose this orange rectangle. I’ll click on OK and I see it here in this list. I’m all done configuring it, so I’ll click on OK. Here now I see the button on my quick access toolbar and when I click on that, that also applies the formatting, so there are many different ways to run your macros. You can choose whichever one you prefer. Now that we’ve created our macro, you might want to save your workbook and this is a little bit different than saving your standard spreadsheet. Let’s go up to the file menu in the top left hand corner and then click on Save As. Here, within the file type drop down, typically you’ll save as an .xls file, but since we have a macro associated with this workbook, we need to save it as an Excel macro enabled workbook or .xlsm. Select that as your file type and then click on save. When I try to open my workbook here I see this and ask the warning message up on top and it tells me that the macros have been disabled. I created this macro so I’m not really concerned about the security of it, so I’ll click on enable. When I click on enable. It asks me if I want to make this a trusted document. If I make it a trusted document, when I open it in the future, I won’t see that bar up on top again, so I’ll click on yes. When you open a workbook like this, you’ll want to make sure that it’s from a trusted source. If it’s from an untrusted source, there could be malicious code included. When I recorded this macro, I used something called an absolute reference. What this means is every single time I ran this macro, it always highlighted this one specific row. But what if I wanted to highlight whatever row I happen to be on? Instead, I can use something called a relative reference. To use a relative reference, first off, let’s remove the formatting on this sheet. Once again, I’ll go up to clear formats. Next, let’s select an active cell. Here I’ll select this one right here. Let’s now go up to View on the top tabs. Go over to macros and let’s view all of the macros. Here I’ll delete the macro that we’ve already recorded called HighlightNewOrders. Here I’ll click on yes, let’s now go back up to macros, and at the very bottom there’s the option for use relative reference. Let’s select that. Let’s now go back up here again, and let’s click on record macro. I’ll type in HighlightNewOrders. Once again, I’ll set the shortcut key to Control + Shift + N, and for now I’ll save it in this workbook and I’ll leave the description blank. Now I’ll click on OK. The macro will now record relative to where my active cell is. This is currently the active cell. I’ll highlight this row, I’ll go up to home and once again, I’ll apply the formatting, so I’ll set it to bold, italics, underline, and I’ll set the fill color. I’m now all done applying my formatting. I’ll go back up to view and once again I’ll stop recording. Now that I’m done recording, I’ll go up to home and let me remove the formatting on this row and here I can now press my shortcut key Control + Shift + N. That applies the formatting. I’ll go down a row, press Control + Shift + N, that applies the formatting on the next row. So once again it’s no longer using an absolute reference where it only highlights one row. It applies the formatting relative to where the starting position is. Congratulations, you just created your very first small program using VBA, wasn’t that easy? Next, I want to show you how you can look at the code that you just created. To view the code that you just created, let’s go up to the top tabs and let’s click on the option that says View. Once again, all the way on the right-hand side, let’s click on this drop down under macros and click on view macros. Here we see the macro that we created and we can click on edit. This opens up the Visual Basic for Applications editor. I’m going to close this window. We can also open that exact same window by pressing the Alt together with the F-11 key. That opens up the same exact window. And you can press Alt + F11 to toggle back and forth between Excel and your Visual Basic Editor. So here I’ll go back and forth. Next, I want to orient you to what we see here in the VBA editor. Over on the right-hand side, this is all of the code that we just generated when we recorded our macro. If you don’t see this code window, you can go up to the view menu and you can select code and that’ll turn this on. Over on the left-hand side we have the project explorer. If you don’t see this by default, you can go to view and here you can select project explorer. Here we see all of our different Excel objects, so I see all of my sheets and I see the workbook. Down below, I see modules and every macro is a module. Over on the left-hand side, I can use the plus and minus sign to expand and collapse these different categories. Down in the bottom left-hand corner, I can also see the properties associated with any of these objects. If I don’t see properties down here, I can go up to view and right here I can open up the properties window. Let’s focus back over on the right-hand side where we have our code and before we dig into this, let’s adjust the font size to make it a little bit easier to read. To adjust the font size, let’s go up to tools on top, select options and then select editor format. Right over here you could select the font and you could also select the size. I’ll go with size 18 and then click on OK and that’s a little bit easier to read now. Let’s now dig into the code. Up in the top left-hand corner it says sub highlight new orders. So what is sub? Well this stands for subroutine and your macro is a subroutine, and it’s all packaged together as a unit. So here you see the top sub and at the bottom you see the end sub. And here’s all of our code in between. Next, in the code we see this section with single quotes and green text. So what is this? These are comments that explain what the code does. Right up here we see the name of the macro and we see that it’s referred to as a macro and right down below we see a keyboard shortcut, and these comments don’t at all affect the code. So here I can type in a single quote and I can say I can type my own comment and once again this won’t impact our code at all. It’s a good practice, especially if you have a complicated macro to include comments that explain what the different sections of your code do. Next up in the code, here I can see ActiveCell.Range(“A1:E1”).Select and this is when I selected the cells. Now you might think I’m selecting these specific cells, but remember I turned on a relative reference, so here’s looking at the active cell that I have selected and then it chooses a range equivalent to cells A1 through E1, and then it selects it. The next ones are pretty self explanatory. Here I set them to bold. I also set them to italics and then I also underline them and in this big section of code here this sets the fill color. So those simple formatting actions that we took is represented by all of this code right here. Now we have complete control over this code and we can make modifications here and that’ll in turn modify the macro. So let’s say maybe I don’t want to set it to bold anymore. Instead of saying true here, I could change this to false. I’ll press Alt + F11 to go back to my workbook. And here, on the very last row, once again I’ll run my macro and here you’ll see that it’s no longer bolded. And look at that, we have complete control over the actions from this code. You’ll probably create most of your macros simply by recording, so you don’t really have to know much about the code behind it, but I still recommend that every time you record, you look at the code. That way you become more familiar with it and over time you understand it better and better. Up to this point, we’ve been creating and viewing our macros through the view tab and then clicking on macros over on the right-hand side. However, we can turn on another tab called developer that makes this even easier and quicker to record and view your macros. To turn this on, hover over your ribbon and right click. Right here click on customize the ribbon. Over on the right-hand side, you’ll see all of your main tabs. Check the one that says developer and then click on OK. You should now see a new tab up on top that says developer and when you click on this you’ll see all of the different developer tools and on the left-hand side we have all of our macro tools. So this makes it even quicker to get to these different commands. The macro that we created so far is attached to this one specific workbook. But let’s say that we want to access our macro from anywhere, so whatever spreadsheet you happen to have open. We can create our macro in something called the personal macro workbook. To do this, let’s go up to the top tabs and click on the one that says developer. Once again over on the left-hand side, let’s click on record macro. Here I’ll give it the name HighlightNewOrdersEverywhere. I won’t enter in a shortcut key for now and for store macro in, I’ll select this drop down and this time instead of choosing this workbook, I’ll place it in the personal macro workbook. I’ll skip the description and then click on OK. Now that we are recording, I’ll run through the same steps again. Here, I’ll click on the home tab. I’ll highlight this row and then I’ll set it to bold, italics, underline and I’ll set the fill color to orange. I’ll go back up to developer and now I’ll click on stop. I’ve now opened up an entirely new workbook that doesn’t have any macros attached to it. Here I’ll click on the developer tab. I’ll click on macros and here I see my HighlightNewOrdersEverywhere macro. Once again, this macro is available in any workbook that I have. Here I can double click on it and here that just ran the macro. Now let’s say I want to edit my personal macro workbook. Here I’m in the developer tab and I can click on macros and here I see my HighlightNewOrdersEverywhere macro. However, if I click on edit, I get this error message that says I cannot edit a macro on a hidden workbook. When I create a macro in the personal macro workbook, it’s created it in a workbook behind the scenes and so I can’t edit it this way. So then how can I edit one of these macros? Well, just like we did before, we can press Alt + F11 or we can launch Visual Basic here from the ribbon. Within the Visual Basic editor over on the left-hand side, I now see a new category in the Project Explorer for personal and this contains all of my personal macros. Here when I click into Module1 we can see what I just recorded. As a quick aside, let’s say that you have a macro attached to an individual workbook, but you would like to make this available everywhere. Here I can highlight the code and I could bring it over into this personal macro workbook and I could paste it in and this will now be available everywhere. Back on the main sheet, next, I want to show you how you can view the code as you’re recording your macro, and to do that, I’ll open up Excel side by side with the Visual Basic editor. I now have Excel open side by side with my editor, but you’ll notice that the ribbon in Excel takes up a lot of space, and so does the quick access toolbar. Luckily I can hide both of those. Here I’ll right click on the ribbon and I’ll select collapse the ribbon. I’ll also right click on the quick access toolbar and here I’ll select to hide it. And that gives me quite a bit of space now to see my spreadsheet and the code. I now want to record a macro that removes the formatting. Just like we’ve been doing all along, let’s go up to developer. Here I’ll set it to use a relative reference and I’ll make sure that I have this cell selected where I have the formatting that I want to remove. Next I’ll click on record macro. Right here for the name, I’ll type in ClearFormatting. I won’t set a shortcut key and here I’ll just save it in this workbook. And I won’t enter a description. Next, I’ll click on OK. Right up above you’ll notice that it now added a second module and this is now for the ClearFormatting macro. It’s now recording and let me run through the steps of removing the formatting. Here I’ll highlight this row and you see the code show up right up above. Then I’ll click on the Home tab and over here I’ll select clear and clear formats. Right up here you can see that these two commands will clear the formatting from these selected cells. That’s all I want this macro to do, so I’ll go back to the developer tab and here I’ll click on stop recording. But I don’t want to create two separate macros, one that highlights and another one that removes highlighting. Instead, I want to set up a toggle, so if it’s already highlighted, it’ll remove the highlighting, and if there is no highlighting, it’ll highlight it. And to do this I can use an if statement. Let’s jump back into the code. Here I am back in the Visual Basic editor and I’m currently in module1 and I’m looking at the HighlightNewOrders macro. Down below in the code, first I select a set of cells and next I want to check have I already applied formatting to those cells? And we can use an if statement to check that. Here I’ll type in if and just for simplification, I’ll only check whether it’s been bolded, and if it hasn’t yet been bolded well, then I want to make it bold. So here we’ll type in if selection.font.bold, so that’s simply reusing this line down here, equals false, so if it’s not yet bold, well then we take all of these actions down here, so I’ll make it bold ,I’ll make it italics, underline, and I’ll set the fill color. Down at the very bottom, I have to say what will happen if it’s already highlighted and it’s already bold. Here I’ll enter down and I’ll type in else. Here I’ll enter a colon and now I have to say, well, what happens if it’s already highlighted. Here if I click into Module2, we already recorded the code for this. This will remove the highlighting. I’ll copy this. I’ll go back to Module1 and here I’ll paste it in. I’ll tab this out and here. I’ll select all this code as well and tab that out. This makes it a little bit easier to read the code. At the very end, to close out my if statement here, I’ll go back and I’ll type in End If. And this now creates a toggle, so if it’s not highlighted, it’ll highlight it, and if it’s already highlighted, it’ll clear the highlighting. Here I am back on my Excel sheet and it’s the moment of truth. We’re going to test whether this works as expected. I’m going to press Control + Shift + N, my shortcut key for this macro and there it applies the formatting. But we did that before. What if I press it again? Once again, I’ll press Control + Shift + N. This will run the macro again, and because those cells had highlighting, it now removes the formatting. Pretty cool. With this example, we took two separate macros that we recorded independently and then we brought them together into one macro, and that’s a fantastic strategy for building together code. Simply record independent pieces and then you could pull them all together. So far, we’ve just been highlighting one row, but what if I want to highlight multiple rows? Here once again I have the Visual Basic editor open side-by-side with Excel and I want to highlight multiple rows. Once again I’ll click on developer and here I’ll make sure that I have use relative reference turned on. I’ll click on record macro and this time I’ll call it HighlightNewOrdersForLoop. I won’t set a shortcut key. I’ll leave it stored in this workbook and I’ll leave the description blank and then click on OK. Right up above, you can see that I’ve now created a new macro and I’m going to go through and apply these same formatting that we’ve been doing all along. Here I’ll click on the home tab. Here I’ll select this row and here I’ll apply the bold, italics, underline, and the fill color, so once again we can see all of the code right up above. Here I’ll select the next row. I’ll highlight all these cells and once again I’ll apply the exact same formatting that I applied before. And if we look closely at this code, you probably notice some repetition. Here I’ll select yet another row, and here I’ll highlight all of the cells and once again I’ll apply all this formatting. I’m now all done recording my macro. I’ll click back onto developer and then I’ll click on stop recording. You’ll notice in the code there’s a lot of repeating code and instead of us recording the same step again and again and again, we can let Excel do that for us using a for loop. I’ve now expanded the code so it uses up my full screen and let’s just take a quick look at what’s happening here. Once again I select a row and here I apply all of the formatting and as soon as I apply the formatting, I go to the next row and then I do the same exact thing. And here once again I go to the next row and then once again I apply the formatting. From this point down, this is all repeating what we’ve already done up above, and I don’t want to have repeating code. Instead, I can use a for loop to tell Excel to do this as many times as I want it to do it. Right down below, let’s delete all of the repeating code. Right from this point downwards, let’s delete all of this. Now that I’ve removed all of the repeating code, I’m ready to tell Excel how many times I want Excel to run this code, and once again we’re going to use a for loop to do this. To set up a for loop, you simply start by saying for and I’ll type in a variable name. Let’s go simple and just use counter. I’ll set it equal to 1 and we’ll go through four times. So with this I’m telling Excel that I want to repeat this code four individual times. At the very bottom I have to close out the for loop, so I’ll type in next and counter. So it’ll go through one time and then it hits next counter and it’ll increment it to two. It’ll go through again. It will increment it to three. It’ll go through again, it’ll increment it to four and then this code will stop. So I could tell it exactly how many times I want Excel to run through this code. Let’s now go back into our Excel sheet, I’ll press Alt + F11 and let’s test this out to see if it works as expected. First I want to highlight all of these cells and I’ll remove the formatting. Now let’s go up to the developer tab. Let’s click on macros and here I see my new macro titled HighlightNewOrdersForLoop. Let’s click on run. And look at that, it goes through row by row four times and it highlights all of those rows. In this example, we specify exactly how many times we want the for loop to run, but what if we want to make it dynamic so the user can choose. For that we can use an input box. Back within the Visual Basic editor, here once again I can see my for loop, except this time I want the user to specify how many times this code should run, and for this we’re going to use an input box. Here I’m going to create a variable called UserInput. I’ll enter an equals sign and then I’ll type in InputBox. This will force an input box to appear where the user can enter how many times they want it to run. Here I’ll open up the parentheses and now I can enter text in for the prompt and I’ll simply type in enter number of rows. Then I’ll close my quotes, insert a comma, and now I could enter a title for my prompt. Here you can see all of the different items that you can customize for this input box. Here, I’ll simply say rows to highlight. Then I’ll close my quotes and I’ll close my parentheses. Whatever value the user enters in, that will be stored in UserInput. Down here for the counter, I want it to run to match whatever the user input is. So instead of entering 4 here, I’ll type in UserInput. So let’s say someone enters 6 in. Here it’ll run 1 through 6. Now that we’ve made these tweaks, let’s jump back into Excel to test it out. Back within Excel, let’s now test this out. I’ll go up to the developer tab, let’s click on macros and here I see my macro HighlightNewOrdersForLoopWithInput. Here I’ll click on run and I now see a prompt that allows me to specify how many rows I want to highlight. Here I’ll type in 6 and then I’ll click on OK. And here we see that it went through 6 times and highlighted all of these rows. With the for loop, you have to specify how many times it should run. But maybe we don’t know how many times it should run? Instead we can use something called a do while or do until loop. These loops will continue running until a certain condition is met that you specify. Here back in the code, I’ve created a new macro called HighlightNewOrdersDoWhile and at least right now this is the exact same code as what I used for the for loop, except this time I want to convert this to a do while loop, so once again it will continue running until a certain condition is met. Here, I’ll remove the for portion and also the next counter, and to create a do while loop, well, you simply type in do while and here I want it to continue running until it hits a blank cell, so I’ll type in ActiveCell. We’ve used ActiveCell before. All you have to do is look one line down and I’ll say until it’s not equal to blank. This is the not equal sign. At the very bottom, to close out this loop, I can simply type in loop and that’s all I need to do. Let’s go back into Excel. Within Excel, once again, let’s go up to the developer tab and over here, let’s click on macros. Right here I can see my new macro titled. HighlightNeOrdersDoWhile and here I’ll click on run. Here you’ll see it goes through and it highlights every single row until it reaches the bottom, at which point it finds an empty cell. Back in the Visual Basic editor, I want to show you yet one more type of loop that you have access to and this one is just a slight variation on the do while loop. This one is called do until. I created a new macro titled HighlightNewOrdersDoUntil. Down below instead of saying do while, we’ll change this to do until. Here I’ll remove this portion and we’re going to do this until we find that the active cell is empty. Here I’ll type in IsEmpty. This is a function that’s available within Visual Basic and here I’ll check is the active cell empty, so I’ll type that in. Down below we close the loop with loop once again and that’s all it takes. You can choose whichever loop you want to use that meets your needs. Within Excel, o nce again, let’s test this out. I’ll go up to the developer tab up on top. I’ll click on macros and let’s click on the one that says do until. Here I’ll click on run and it works the same way as the do while loop. It’s just a different way of structuring it. You now have the fundamentals of creating macros. You know how to create if statements you know many different types of loops. Next, let’s bring everything together in a more complex example, and although I say complex, you have all the skills and tools necessary to be able to pull this off. Here I am back in Excel and I have a whole bunch of order information for the Kevin Cookie Company on the bring it all together sheet and if you look at this the formatting is not really that clear. Here I continue repeating the header and then I have the data over here. I would much rather format it so it matches the format that appears on the basic macros worksheet, and let’s imagine that maybe I get a new set of orders every single week, so every single week I end up manually reformatting this data. I don’t have to do that anymore because now we know all about macros and we’re going to use everything that we just learned to get all of this data into the proper format. To make this as easy as possible, we’re going to break this up into chunks, and if I click back on the other sheet, first, I want to get the headers so they appear properly. Back on the bring it all together worksheet, let’s go up to the developer tab, and over here, let’s record a macro. Also make sure that use relative reference is turned off and we’re using an absolute reference. Here I’ll click on record macro. For the macro name, I’ll call this TransposeHeaders. I won’t select a shortcut key and I’ll leave it in this workbook, and then I’ll click on OK. Over on the left hand side, here I’ll highlight all of the unique header items, so these five different rows. I’ll copy it and then I’ll click over into cell D1. I’ll click on the home tab up on top, I’ll click on the drop down under paste. I’ll go down to paste special and right here I’ll select transpose. So this will transpose it from a vertical orientation to a horizontal orientation. That’s all I need for the TransposeHeaders macro, so I’ll click on the developer tab up on top. Then I’ll click on stop recording. To make sure that the macro works properly, let’s test this out. I’ll select columns D through H. I’ll right click and let me delete all of the contents. I’ll go up and click on macros and let’s run the new TransposeHeaders macro. I’ll click on run and there it successfully transposes the headers. This is exactly what I expected. Now that I’ve pasted in the headers, next I want to transpose the customer information and this will be our second macro. At the end, we’ll bring all of these macros together into one. Once again, let’s go up and click on record macro and for this macro I’m going to call it TransposeCustomerOrders. I also want to make sure that it’s set to an absolute reference. I won’t set a shortcut key and I’ll store it in this workbook. Then I’ll click on OK, and here I’ll select cell B2, the first bit of customer information and one thing I’m going to change now is I want to now set this to a relative reference and here I’ll select all of the customer information. I’ll press Control + C to copy. Then I’ll click over to D2. I’ll go to home. I’ll click on paste and once again let’s go to paste special and let’s transpose this data. So this space in the customer order and there’s a lot of different customer information. We’re going to use a loop to go through all of this information, but for now I simply want to copy and paste over a few of the items, so just like we did previously, we can see the repeating code. Here I’ll select an additional customer order. I’ll press Control + C and then I’ll come over to cell D7 and here I’ll go to paste, paste special and then I’ll click on transpose. Once again, I’ll select one more customer order. I’ll copy, click over into D12, go to paste, paste special, and here I’ll transpose once again. Once we jump into the code, we’ll see some of that code repeating, and that’s where we’re going to insert the loop. You might be wondering. Why am I leaving all of this space in between the different customer orders. Shouldn’t I just paste right underneath the previous one? Well, once again we want the code to be repetitive and here if I copy this item and then I paste it up here, well, each time that I paste it, the spacing would be slightly different and that’s not repeatable with code. So here we’ll simply paste it directly to the right of the top line of the customer order and later on, we’ll record an additional macro that’ll remove these spaces. I’m now done recording this code. I’ll go up to the developer tab and let’s click on Stop recording. Before we jump into the code, let’s test our macros to see how they work so far. Here once again, I’ll select columns D through H, I’ll right click and let me delete all of the contents. I’ll go up to macros and first we want to transpose the headers, so I’ll run that and now we have the headers. I’ll click on macros again, and here let me click on TransposeCustomerOrders. I’ll run that and it works exactly as expected. We’re now ready to add a loop, so we add all of the customer information under these headers, and to do that, we’re going to open up the Visual Basic editor. Just like we’ve been doing all along, let’s press the Alt key together with the F11 key. Here we are back in the Visual Basic editor and we can see the two macros that we just recorded. Here I see the macro for transposing the headers and right down below, here’s the macro and all the associated code for transposing the customer orders. Within TransposeCustomerOrders, I want to set up a loop so it goes through that entire list of all of the customer orders. Let’s look through the code to see where the repeating portion is. Right up here I select cell B2 and this is an absolute reference. The reason we made this an absolute reference is we always want to start from this specific location. From there we switch to a relative reference and then we copy the next five rows of customer information. Then we copy the information and then we transpose it under the headers and right down here we then select the next five rows of customer information. We set Cut Copy mode to false and then we copy the information. So right here I see Selection.Copy and we had that earlier right here. Here if I look at this code all the way down, we just ran through this exact same code up above, so this is our repeating section of code so we can place a loop around this segment of code. So here I’ll delete the repetitive portions all the way down, and if we look up here, once again, this was the repeating portion of code and we’ll place a loop around this. To create the loop once again, we could use any of the loops that we learned about today. You could create a for loop, a do while, or a do until. I’m going to use a do while loop and I’ll simply run this loop until we hit an empty cell. So once we’ve gone through the entire column of customer information. Here, just like we did earlier, I’ll type in, do while and here I’ll type in ActiveCell <> “” To close out the loop down at the bottom here, once again I’ll type in loop and now we have the loop in place and we’re ready to test this code. Now that we’ve set up our loop, let’s test this out to ensure everything is working properly and we can use a new technique where you can test it line by line. Here I have Excel open side-by-side with the Visual Basic editor, so as the code runs we can see the changes happen in Excel. Before we run the code, let’s delete the data that we recorded previously. Here I’ll highlight the data and I’ll go through and delete that, so now it’s completely blank. Now we’re ready to test it. To test it, let’s go into the Visual Basic editor and make sure your cursor is within the TransposeCustomerOrders macro. Right up here there’s a menu called debug and there’s an option called step into. You can also use the shortcut key F8. This will allow us to run our code line by line and as it runs through line by line, we’ll see the changes happening up above. To start, I’ll press the F8 key and here we see that this macro is currently active. I’ll press F8 again and we’ll see each line highlight as that’s the active line of code and as I press it we can see that it’s running through the code and up above you can see the associated changes. That’s pretty cool, right? So here we see that it’s currently in the loop and this will continue running until it hits that empty active cell. Now if you get tired of pressing F8, you could also just run this entire block of code. Right up above, you can press the continue icon, or you can press the F5 key. Here I’ll click on that and it’ll run through the loop until it hits that empty cell. Here when I scroll up, I can see that it successfully copied and pasted the information under the headers. The hard part is now out of the way, but we do have to record one more macro. I have all of these blank rows here and I want to remove them. To remove these blank rows, once again, let’s go up to developer. This time, let’s use an absolute reference because I know my data is in these columns and I want to reference these specific columns. I’ll turn off use relative reference. Here I’ll click on record macro and for this macro I’m going to call this remove blank rows. I won’t set a shortcut key, I’ll store it in this workbook and then I’ll click on OK. Here I’ll select columns D through H. Let’s click on the home tab and on the right hand side, click on find and select go to special and right here we can select all blanks. I’ll select that and then click on OK. With all of the blanks selected, I’ll right click and then I can click on delete. It asks me how I want to delete it. I want to shift the cells up, then I’ll click on OK and here you see all of the data is now here without any blank spaces. Next, let’s go up to developer and then click on stop recording. We’re almost done. We now have our three macros, but we want to combine these three macros into one. This way, when we run the code, it’ll run through all of these steps. Just like we’ve been doing all along, let’s press Alt + F11 to jump into the Visual Basic editor. We’re going to combine these three macros that you see right here. Combining them is pretty easy. Here I’ll take the name of the first macro and I’m going to retitle this CleanUpCustomerData. This will be the macro that contains all of the other macros. Down here instead of ending the macro, I’m going to delete the end subroutine. I’ll also delete the start of the next macro, and by doing that, it’s now combined these two macros. Down below I’ll do the same to the customer order macro. Here I’ll get rid of the end and I’ll get rid of the start of the next one, and this has now effectively combined the three macros into just one macro. Back within Excel, let’s test out this combined macro. Here I’ll highlight these columns. I’ll right click and I’ll delete all of the customer information over here. Let’s go up to macros and here I see my combined macro called CleanUpCustomerData. Let’s click on run and there it goes through and I’ll go down so we get a fresh view. Here I see all of the headers, the customer information, and all of the spaces have been removed. This is exactly the format that I want my data in. As we were running the code, you might have noticed that the screen showed all of the different steps as it was running through that and that slows things down a little bit. To make things even more efficient and fast, we can turn off screen updating. To turn off screen updating, we just need to add 1 line of code. Here I’ll type in application screenupdating = false This way the code will run and we won’t actually see it running on the screen. That’ll help speed things up a little bit. Let’s test it out. Once again, in Excel I’ll delete all of the customer information. Let’s go back up to macros and here all run CleanUpCustomerData and there it just appears. So it goes a lot quicker and we don’t have to see all of the magic happening behind the scenes. One thing I want to call out before I leave you. When you run a macro, there’s no going back. You can’t undo the macro. So if you’re going to run a macro and it’s going to be destructive to your data, use extra caution because once again, you can’t undo the changes. All right, well, that’s how easy it is to start recording and editing macros in Excel. And you probably feel a little bit closer to now becoming a wizard in Excel. To see more videos like this one, check out the playlist up above. Also, please consider subscribing and I’ll see you next time.