Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase My Account My Account Blog Blog

My First Macro

If you don’t see the “Developer” Tab on your Ribbon, click here to see how to enable that Tab.  It is disabled by default. Our first macro will provide a simple way to copy only the visible values from a set of data. Often, you will hide, filter or group data sets, so that only some of the rows or columns are visible.  You then want to copy just those cells and paste them somewhere else.   However, (with earlier versions of Excel), when you go to paste the range you just copied, you find that all the rows that were hidden are now in your pasted version!  How to get JUST the visible cells?
I’ll first walk you through the manual solution, then we’ll record a macro to do the steps for us. We’ll work with a large data set which has daily revenue data from 6 years and 11 stores. Here’s what the first few rows look like: We’re going to hide all the city columns, so we just have the first three columns (Date, Year, Day) and the last, total, one (Corporate).  Like this:
As you can see, the columns D through N are hidden (indicated by the green vertical line). If we were to copy and paste this into another worksheet, the hidden rows would be copied along, unhidden upon pasting, and we’d have the same thing we have above. Here’s how we get around that:
To start with, Highlight the section we want, then click CTRL + G (aka “Go to”), which gives you this pop- up window: Notice the “Special...” button at the bottom left.  Click that to get our next pop-up window:
Notice the cursor hovered over “Visible cells only”.  Selecting this means that only the visible cells will be selected; hidden cells are ignored. Click OK, and our selection looks a subtle bit different:
Yes, subtle, but there is now a thin line between column C and column O.  This indicates that you have selected those cells ONLY, and have not selected the hidden cells in- between. Hit “CTRL + C” to copy, then move to a new sheet to paste:
Success!  Our selected section copied over into the new worksheet, and you’ll notice there are no hidden columns - they have been left behind on the old worksheet. So, that is useful, but kinda long, and you keep moving between the keyboard and mouse (you can use keyboard shortcuts, but they’re not easy to remember). To speed things up, we’ll create a macro.
On the Developer tab, you’ll see the Code Group.  This is your entrance to macro building and editing (and, ultimately, troubleshooting :)).  The control we want is in the upper right - “Record Macro”.  We’ll click that, and receive our next popup: This is a pretty simple popup.  You can ignore everything for now; we’ll go over the options here in more detail on the Personal Macro Workbook page. For now, put a “D” in the box beside “Ctrl +”, then just click OK to get the macro started.
When you click “OK”, the popup disappears, and it looks like nothing happened.  The ONLY way you can tell that you are recording a macro is that the “Record Macro” button on the Code Group has changed to “Stop Recording”, as you can see, below: No, that’s not the most-intuitive thing on the planet.  I think the idea is to not get in your way, but if this is your first time creating a macro, it can leave you with a “now what?” kinda feeling. OK, so, “Now what?”, right?  Now, we go through all the same steps we followed, above: CTRL-G Click “Special...” Choose “Visible cells only” Click OK Now you are ready to go back to the Developer tab, in the Code Group, and click “Stop Recording”.  Congratulations!  You just recorded your first macro.  Now, highlight some cells with a hidden row or column, and press “CTRL + D” - that runs your macro! To continue, take a look at the “Personal Macro Workbook” page to learn more about that macro, as well as Beginning VBA” to see what was recorded, and how to edit it. You’ve taken your first, small step towards massive increases in your personal productivity.  Well done!