Author: Oscar Cronquist Article last updated on April 09, 2018

In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of cells manually selecting those cells, one by one will be tedious and time-consuming.

Luckily there is a wonderful trick that will save us lots of time, the following steps demonstrate how to select blank cells in a cell range:

  1. Select cell range D3:D15.
  2. Press function key F5 on your keyboard.
  3. Press with mouse on button "Special...".
  4. Select "Blanks"
  5. Press with left mouse button on OK button

The picture above shows all blank cells selected in cell range D3:D15.

Read the following article on how to enter a formula or data in all selected cells:

VBA Macro

The following macro will be handy if you often find yourself often selecting blank cells in a specific cell range.

Sub Macro1()
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

Where to copy code?

  1. Copy above macro
  2. Go to VBA Editor (Alt+F11)
  3. Press with left mouse button on "Insert" on the top menu
  4. Press with left mouse button on "Module" to insert a module to your workbook
  5. Paste code into the code window
  6. Exit VBA Editor and return to Excel (Alt+Q)

Save your workbook

To be able to use the macro next time you open your workbook you need to save the workbook as a macro-enabled workbook.

  1. Press with left mouse button on "File" on the menu, or if you have an earlier version of Excel, press with left mouse button on the office button.
  2. Press with left mouse button on "Save As"
  3. Press with left mouse button on file extension drop-down list
  4. Change the file extension to "Excel Macro-Enabled Workbook (*.xlsm)".

Tip! Link the macro to a button on the "Quick Access Toolbar"  to have it freely available when needed.

How to add a macro to your Excel Quick Access Toolbar

The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]

How to add a macro to your Excel Quick Access Toolbar

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

How to find errors in a worksheet

Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

How to quickly select a non contiguous range

A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]

Remove print preview lines (Page Breaks)

Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]

How to find blank cells

In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]

How to add a macro to your Excel Quick Access Toolbar

The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]