How to quickly select blank cells
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:
- Select cell range D3:D15.
- Press function key F5 on your keyboard.
- Press with mouse on button "Special...".
- Select "Blanks"
- 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:
Recommended articles
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?
- Copy above macro
- Go to VBA Editor (Alt+F11)
- Press with left mouse button on "Insert" on the top menu
- Press with left mouse button on "Module" to insert a module to your workbook
- Paste code into the code window
- 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.
- 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.
- Press with left mouse button on "Save As"
- Press with left mouse button on file extension drop-down list
- 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.
Recommended articles
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
Blank cells category
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
Excel basics category
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]
If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.