What Is VBA SpecialCells?
The VBA SpecialCells feature is a helpful feature that can allow you to deal with some given cells in Excel, such as formatting, content, errors, or visible cells after filtering. It allows performing operations on selected sets of cells to perform complex functions and is an essential tool for automating work in Excel. Let us look at an example. Suppose we have a database with many cells, and we want to find the last cell with data.


The code first declares a “Range” object to store the result of the VBA SpecialCells method. Next, by utilizing the “xlCellTypeLastCell” constant within the SpecialCells method, the code efficiently identifies the last cell with data within the defined range. Finally, when we run the code, a message box is displayed, showcasing the address of the identified cell.

Table of contents
Key Takeaways
- The VBA SpecialCells enables the manipulation of data within Excel ranges selectively according to predetermined standards, including visible cells following filtering, cells containing constants, formulas, or specific formatting, and non-blank cells.
- The VBA SpecialCells enables more accurate data analysis and manipulation activities by focusing on specific sorts of cells within a range.
- When utilizing the VBA SpecialCells, it is important to handle errors appropriately, particularly in situations where no cells match the required criteria.
- When it comes to handling non-blank cells, visible cells, cells with particular content types, or formatting, the VBA SpecialCells method provides various options for selecting cells based on different criteria.
Syntax
The syntax for using VBA SpecialCells is as follows:
Range.SpecialCells(Type, Value)
Range: The range of cells you want to work with.
Type: A constant that specifies the type of cells you want to select (e.g., xlCellTypeBlanks, xlCellTypeConstants).
Value: An optional parameter that allows you to specify additional criteria based on the selected type.
The following are the VBA Special Cells types:
Special Cell Type | Description |
---|---|
xlCellTypeAllFormatConditions | All formatted cells |
xlCellTypeAllValidation | All cells with data validation |
xlCellTypeBlanks | All blank cells |
xlCellTypeComments | All cells with notes |
xlCellTypeConstants | All cells containing constants (numbers or text) |
xlCellTypeFormulas | All cells with formulas |
xlCellTypeLastCell | The last cell in all used ranges |
xlCellTypeSameFormatConditions | All cells with the same formatting, including conditional formatting |
xlCellTypeSameValidation | All cells with the same data validation |
xlCellTypeVisible | All visible cells |
How to Use VBA SpecialCells?
- Open Excel and press ALT + F11 to open the VBA editor.
- Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
- In the new module, first start by defining a subroutine to encapsulate the functionality you want to perform using SpecialCells. For example:
Sub MySpecialCellsRoutine()
‘ Your code using SpecialCells goes here
End Sub - Choose the range of cells you wish to deal with next. By choosing cells, you can implicitly provide the range or explicitly express it using the Range object.
- Choose the range of cells you wish to deal with next. By choosing cells, you can implicitly provide the range or explicitly express it using the Range object.
Dim selectedRange As Range
Set selectedRange = Range(“A1:A10”).SpecialCells(xlCellTypeBlanks) - After utilizing the VBA SpecialCells to pick the required cells, you can work with them in a number of ways, including formatting, copying, removing, and extracting data.
- Close the VBA editor and return to your Excel workbook. Select the macro containing your VBA SpecialCells code and click “Run” to execute the code.
Examples
Example #1-Select Cells with Notes
Suppose we have an Excel table with data and one of the cells containing a comment and we want to trace that cell using the VBA SpecialCells method. In this example, we will see how to make use of the VBA SpecialCells method to select cells containing comments or notes within the used range of the active sheet.

Step 1: In the new module, we first define a subroutine named “SelectCellsWithNotes.”

Step 2: In this step, we write a code to select cells that contain comments. Here, we write the following line of code that performs the required action for us.
“ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Select.”

Let us break it down further to understand the line of code:
- ActiveSheet: This refers to the currently active worksheet in Excel.
- UsedRange: This method returns a Range object representing the used range of cells on the active sheet, which includes all cells that contain data or formatting.
- SpecialCells(xlCellTypeComments): This selects only those cells within the used range that have comments or notes.
- Select: Finally, the “.Select” method is called on the selected cells, which highlights them in the Excel workbook.
Step 3: Now, save the VBA macro and click on Run. When you run this code, it selects all cells in the used range of the active sheet that contain comments or notes. In this instance, as there is a comment mentioned at Cell A1, the code selects Cell A1.

Here is the full code:
Sub SelectCellsWithNotes()
‘ Select cells with comments/notes
ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Select
End Sub
Example #2-Select Cells with Conditional Formatting
In this example, we will see how to use the VBA SpecialCells to select cells with conditional formatting applied on the active sheet. For this example, we have applied the conditional formatting to the cells B2 to B6.

Step 1: Similar to the previous example, this line defines a VBA subroutine named “SelectCellsWithConditionalFormatting.”

Step 2: Select Cells with Conditional Formatting
In this step, we use the following line of code to act for selecting cells with conditional formatting:

Let us breakdown the code to understand further:
- ActiveSheet: Refers to the active worksheet.
- Cells: Refers to all cells on the active sheet.
- SpecialCells(xlCellTypeAllFormatConditions): This selects cells within the active sheet that have any type of conditional formatting applied.
- Select: Finally, the .Select method is called to highlight the selected cells.
Step 3: Now, click on Run. When you run this code, it selects all cells on the active sheet that have conditional formatting applied. In this case, the cells B2 to B6 are selected.

Here is the full code:
Sub SelectCellsWithConditionalFormatting()
‘ Select cells with conditional formatting
ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions).Select
End Sub
Example #3-Cells with Data Validation
In this example, we will see how to use VBA SpecialCells to identify and select cells with data validation rules specifically applied to the “Year” column, displaying a message if no such cells are found.

Step 1: After creating the new subroutine, we start by declaring a variable named “rng” as a Range object. This variable will store the range of cells with data validation rules applied to the “Year” column.

Step 2: Next, we temporarily set the error handling method to ignore errors. If an error occurs during the execution of subsequent lines, the code continues to execute rather than halting.

Step 3: Now, we write a code to set the range with Data Validation rules:

Let us further understand what each component in the code means:
Range(“C:C”): Specifies the range of cells in column C, which represents the “Year” column.
.SpecialCells(xlCellTypeAllValidation): Filters the cells in the specified range to only include those with data validation rules applied.
Step 4: In this line, we reset the error handling to its default behavior. If an error occurs after this line, it will halt the execution of the code.

Step 5: Here, we check if the rng variable is not empty, which means that cells with data validation rules were found in the “Year” column.

Step 6: If cells with data validation rules are found, this line selects those cells.

Step 7: If no cells with data validation rules are found in the “Year” column, this block of code displays a message box indicating that no such cells were found.

Step 8: When the code is run, it selects cells in column C (representing the “Year” column) that have data validation rules applied, and if found, it highlights those cells; otherwise, it displays a message indicating no cells with data validation rules are found in the “Year” column.

Here is the full code
Sub SelectCellsWithDataValidation()
Dim rng As Range
On Error Resume Next
Set rng = Range(“C:C”).SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Select
Else
MsgBox “No cells with data validation rules found in the ‘Year’ column.”
End If
End Sub
Important Things To Note
- Be cautious when using the VBA SpecialCells, as it can lead to unexpected results if not used carefully.
- Avoid using the VBA SpecialCells within a loop, as it can be resource-intensive and slow down your VBA code.
- You can use “VBA Count” or “CountLarge” functions to count the number of cells that meet certain criteria using SpecialCells.
- The VBA Special Cells Not Blank feature allows you to select cells within a range that contain data or constants and are not blank.
- The VBA Special Cells Visible method enables you to work specifically with the subset of data that meets the current filter criteria, ignoring any rows or columns that are hidden due to filtering.
Frequently Asked Questions (FAQs)
When using VBA SpecialCells, errors can be handled by utilizing error handling strategies such as using the “On Error Resume Next” line before attempting to utilize the VBA SpecialCells and then using “On Error GoTo 0” to reset error handling and check for errors afterward.
Furthermore, you can retrieve error information and take necessary action based on the error observed by using the “Err” object.
It is crucial to choose a more precise range to work with when using the VBA SpecialCells to prevent accidentally choosing entire rows or columns.
Narrow down the range to the precise region where you wish to search for special cells rather than applying the VBA SpecialCells to the entire worksheet or to entire rows/columns.
Using VBA SpecialCells, you can use the “Count” property of the resultant Range object to count the number of cells that match specific criteria after using SpecialCells to pick the desired cells.
For example, you can use “SelectedRange.Count” to find the number of cells that match the requirements after selecting cells using data validation rules.
Yes, the VBA SpecialCells allow you to filter cells based on many criteria. It could be necessary to make several calls to SpecialCells using various criteria, and then aggregate the outcomes as required.
As an alternative, you can filter cells using more sophisticated criteria by utilizing additional VBA functions and methods including loops, conditional statements, and array manipulation.
Download Template
This article must help us understand the VBA SpecialCells formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to VBA SpecialCells in Excel. Here we explain how to create & use statusBar using VBA code, along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply