6. Remove Blank Cells with Macros
Blank cells appear in most columns and can be frustrating to remove, particularly in large datasets. To save time, let's build a Macro to remove any blanks in a column of data.
3 steps to building a Macro that removes blanks
1 Select a number of rows, say the first 8
2 Select the blank rows within this selection
3 Delete the selected blank rows
Pre-selecting cells before running a Macro
- It's important to always pre-select cells before running a Macro
- Otherwise, it will not work correctly when you try to repeat the action
- Also, be aware that UNDO is not available so create a copy of your file in case errors are made
F5: Open Go To dialogue box
CTRL+-: Delete selected cells
CTRL + SHIFT + →: Select all cells within data region
ALT + L , R: Stop / Start recording Macro
ALT + F11: Open Visual Basic Editor
Now that you know how to create, edit and delete macros, let's explore some useful applications of this Excel tool. The task we're going to perform in this lesson using macros is to remove all of the blank rows from a column of data. In this Excel sheet, I have well over 100 customer names, but unfortunately, some blank entries appear every so often. This is a common problem you may encounter when analyzing data sets exported from a server. We're going to follow three steps in creating our macro. First I want to select a small number of rows, say, seven or eight. Next, I'm going to select the blank cells within this selection, and third, I am going to delete them. I'll start by selecting the first eight cells and then I'll press Alt + L and then R to start recording my macro. And I'll call the macro, removeBlanks. And the shortcut key would be, Ctrl + Shift + R. Then I'll press OK. The first step will be to select the blank cells within this selection. So I'll press F5 to open the GoTo dialogue box and then I'll hit S for Special. Here, I'll select blanks and then press OK. And as you can see we now only have the blank cells selected. I'll delete these with Ctrl + - (minus) and I'll shift cells up. So I'll press OK and we've now removed the blanks from our column. I'll now stop recording and see if this works for other cells as a macro. So I'll select another couple of cells and use my macro, Ctrl + Shift + R. And as you can see it removes all of the blanks, exactly what I want. I'll select the remainder of the dataset. There we go. And click Ctrl + Shift + R again. And now we have the full dataset with no blanks, exactly what I want. You might have noticed that before running the macro I first selected the cells that I wanted to apply the macro to. If I don't do this, the macro doesn't work correctly. So when you're creating macros, always make sure that you preselect the cells you want to perform the macro on, before recording the macro. Before I wrap up this lesson, let's take a quick look at the VBA code that created our macro just so we get some practice reading this code. So I'll press Alt + F11 to open the Visual Basic editor and as you can see we have two lines in our macro. The first line takes our initial selection and finds the special cells that are blank and then selects them. These blank cells now become our selection. In the second row, the blank cells are then deleted and we move up one cell after the deletion. As you can see, this is pretty straight forward even if you haven't looked at VBA code before.