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.
