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'm going 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'm 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 remove blanks. And the shortcut key will be control shift R.
And 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 go to dialog 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 control minus.
And I'll shift cells up. So I'll press OK, and we've now removed the blanks from our column.
And 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 control shift R. And as you can see, it removes all of the blanks, exactly what I want. I'll select the remainder of the data set.
There we go.
And click control shift R again.
And now, we have the full data set 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 pre-select 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 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 straightforward, even if you haven't looked at VBA code before.