How to Select Only Cells With Data in Excel VBA

Excel VBA (Visual Basic for Applications) is a powerful programming environment developed by Microsoft for automating and developing applications in Excel. A common task in Excel VBA is selecting cells or ranges that contain data. This guide will guide you on how you can select only cells that contain data using Excel VBA.

In VBA, a programmatic interface to Excel documents, a Range object is commonly used to represent a cell or collection of cells. We will be utilizing this object in our code to perform the task.

Step 1: Start With a Sub Procedure

Begin with a Sub Procedure. Sub Procedures in VBA are the equivalent of functions used to carry out calculations and manipulate data in Excel. You will need to define it using the Sub statement, followed by the name of the procedure.

Step 2: Define the Range

You need to define the range in which you want to select the cells with data. This can be done using the Range object and specifying the range in the format (“A1:Z100”).

Step 3: Utilize the SpecialCells Method

To select only cells that contain data, VBA provides a handy method called SpecialCells.
Use this method with the argument xlCellTypeConstants as shown in the below code.

Step 4: End the Sub Procedure

Finally, you need to close the Sub Procedure. This can be done with the End Sub statement.

The Full Code

Here is the complete code that we have developed to select only cells containing data.

After executing the above code, Excel will select only those cells in the range A1:Z100 in Sheet1 that contain data.


This guide shows you how to use Visual Basic for Applications (VBA) to select only cells in Excel that contain data. By using VBA’s powerful programmatic interface, this task becomes simplified, enabling you to work more efficiently.

Remember, while the code provided in this article is ready to use, you might need to adjust the workbook name, worksheet name, or range to fit your specific needs. Additionally, always ensure to test the code on a copy of your workbook to avoid unwanted consequences in your original document.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba