Today we’ll learn about how to easily check whether one or multiple cells are empty using Visual Basic for Applications. The content is relevant for Excel 2019 / 365, 2016 and earlier versions of Microsoft Office.
Before starting coding with VBA in Excel you’ll need to ensure that your development tab is enabled. If that’s not the case, kindly look into this short tutorial.
Checking if Excel cells range are empty with VBA
- In Excel, open your Visual Basic environment by hitting Alt+F11
- Insert the following code as a procedure in your Worksheet, or alternatively in a dedicated VBA module.
Sub Check_Cell_is_empty_alt() Dim rng As Range Set rng = Selection For Each Cell In rng If IsEmpty(Cell.Value) = True Then Debug.Print ("Empty") Else Debug.Print ("Not Empty") End If Next Cell End Sub
- Go to your spreadsheet and select an arbitrary range of cells.
- Go back to the Visual Basic editor and ensure that the Immediate viewer (CTRL+G) is visible – as the Debug.Print method in the code will output its result to that window.
- Hit F5 to run your code.
- Look into the results in the immediate window.
- All blank cells will be identified.
Possible extensions to the code: You can obviously extend this code as need in case that you are interested to skip blank values or just do nothing in case the cells aren’t full. Similarly, you can check whether the Range itself is empty, ie – no black cells are contained.
Checking non empty fields
In the same fashion you can use the IsEmpty property to check for fields that are not blank.
If Not IsEmpty(Cell.Value) = True Then Debug.Print ("Not Empty") Else Debug.Print ("Empty") End If