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 […]
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 […]
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 […]
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.
