30 Advanced Excel Tips for 2026

Excel Champs / VBA / How to use Special Cells Method in VBA in Excel
By Puneet Gogia (Microsoft MVP)

How to use Special Cells Method in VBA in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

SpecialCells returns the cells in a range that match a type, like Home → Find & Select → Go To Special. Range("A1:D100").SpecialCells(xlCellTypeBlanks) gives the empty cells, xlCellTypeFormulas the formula cells, xlCellTypeConstants the typed-in values, and xlCellTypeVisible the cells a filter leaves visible.

Add a second argument to narrow formulas or constants by what they hold: SpecialCells(xlCellTypeConstants, xlNumbers) for typed numbers, xlTextValues for text, xlLogical for TRUE/FALSE and xlErrors for error values. Then act on the whole result in one step, as in .Interior.Color = vbYellow or .EntireRow.Delete.

When nothing matches, SpecialCells stops with error 1004, “No cells were found”. Set the result with errors switched off, then check it:

Dim rng As Range
On Error Resume Next
Set rng = Range("A2:A500").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

Call it on a range of more than one cell. On a single cell, such as Range("A1").SpecialCells(xlCellTypeBlanks), it searches the sheet’s whole used range instead. xlCellTypeLastCell always returns the last cell of the sheet’s used range, whatever range you call it on.

In VBA, with the Special Cells method, you can select a cell or range of cells that are of a specific type and have a specific sort of value. Let’s say you want to know which cells in the range A1:A10 have a formula that returns a number. Well, you can get this result with the SpecialCells method.

Use Special Cells Method in VBA

use special cells method in vba

Here we are writing code to know the address of the last used cell. Note that the SpecialCells method has two arguments, out of which one is required and the other is optional.

  • Type (required): the type of cells you want, like xlCellTypeBlanks, xlCellTypeConstants, xlCellTypeFormulas, xlCellTypeVisible, xlCellTypeLastCell or xlCellTypeComments.
  • Value (optional): only for xlCellTypeConstants and xlCellTypeFormulas, to get the cells with a specific kind of value: xlNumbers, xlTextValues, xlLogical or xlErrors. You can add them together, like xlNumbers + xlTextValues.
  1. First, declare a variable as range to store that cell address returned by the code.
    declare a variable as range
  2. After that, refer to the range and use the “SpecialCells” method where you need to specify the “Type” argument with “xlCellTypeLastCell”.
  3. Next, set that special cell line of code to the variable that you have defined in the first step.
    use the specialcells method
  4. From here, you need to use a VBA message box to get the address of the cell returned by the SpecialCells method.
  5. In the end, use the Address property with the variable to get the address of the last cell.
    Vba message box to get the address

Here’s the full code.

Dim myRng As Range
Set myRng = Range("A1:A10").SpecialCells(xlCellTypeLastCell)
MsgBox myRng.Address

Now when you run this code, it will show you a message box with the address of the last cell of the used range of the worksheet (the cell you get with Ctrl + End). It’s not the cell you typed in last, and it isn’t limited to A1:A10: xlCellTypeLastCell always looks at the whole sheet. So here in my case, the data ends in cell A10, and I got the same in the message box.

Select Cells with Notes

In Excel, old “Comments” are now “Notes”. When you write code to select comments, it will select the notes that you have in the specified range. Consider the following code.

select cells with notes
Dim myRng As Range

Set myRng = _
Range("A1:A10").SpecialCells(xlCellTypeComments)

myRng.Select

Using Both the Arguments

As I said earlier, you have two arguments for the SpecialCells method. Now, let’s see how you can use both arguments to select cells that have a formula, where the value that formula returns is a logical value (TRUE or FALSE).

Dim myRng As Range

Set myRng = Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlLogical)

myRng.Select

Now when I run the above code, it selects the cells from the range A1 to A11 where I have formulas that return a logical value.

using both of the arguments

Select Cells with Conditional Formatting

Range("A1:A11").SpecialCells(xlCellTypeSameFormatConditions).Select
Range("A1:A11").SpecialCells(xlCellTypeAllFormatConditions).Select

The first line selects the cells that have the same conditional formatting as the first cell of the range, and the second line selects all the cells that have any conditional formatting.

Select Visible Cells

And you can also select the visible cells using the “xlCellTypeVisible” constant. Consider the following code.

Dim myRng As Range

Set myRng = Range("A1:A11").SpecialCells(xlCellTypeVisible)

myRng.Select
select cells with conditional formatting

Or you can also use the “12” as the argument value.

Dim myRng As Range

Set myRng = Range("A1:A11").SpecialCells(12)

myRng.Select

Cells with the Data Validation

Range("A1:A11").SpecialCells(xlCellTypeAllValidation).Select
Range("A1:A11").SpecialCells(xlCellTypeSameValidation).Select

The first line selects all the cells that have data validation, and the second line selects the cells with the same validation as the first cell of the range. Like the other short codes on this page, put these lines between Sub and End Sub in a module to run them.

Select Blank Cells

One of the most useful types is xlCellTypeBlanks. The following code selects the blank cells in A1:A11 and fills them with 0. You can use the same idea to highlight or delete the blank cells.

Sub fill_blank_cells()
Range("A1:A11").SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

Note that only truly empty cells count as blanks. A cell with a formula that returns “” is not blank for SpecialCells.

Select Cells with Numbers or Text Only

Use xlCellTypeConstants with the Value argument to get the cells where you have typed a specific kind of value, not formulas. The following code selects only the cells with numbers:

Sub select_number_constants()
Range("A1:A11").SpecialCells(xlCellTypeConstants, xlNumbers).Select
End Sub

Handle the “No Cells Were Found” Error

When there are no cells of the type you ask for, SpecialCells gives run-time error 1004 (No cells were found). To avoid it, turn off error handling for that one line, then check if the variable is Nothing:

Sub special_cells_safe()
Dim myRng As Range
On Error Resume Next
Set myRng = Range("A1:A11").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "There are no blank cells."
Else
myRng.Select
End If
End Sub

There’s one more thing to keep in mind: if you use SpecialCells on a single cell, like Range(“A1”).SpecialCells(xlCellTypeBlanks), it looks at the whole used range of the sheet, not only that cell.

Frequently Asked Questions

Is SpecialCells the same as Go To Special?

Yes. SpecialCells is the VBA version of the Go To Special dialog box (Home ➜ Find & Select ➜ Go To Special), and each type matches one of its options.

Why does SpecialCells select cells outside my range?

Because your range is a single cell, or you have used xlCellTypeLastCell. In both cases, SpecialCells works on the used range of the whole sheet. Use a range of two or more cells to limit it.

Can I use SpecialCells on a protected sheet?

Often, no. SpecialCells can fail with error 1004 on a protected sheet, so unprotect the sheet in your code before you use it and protect it again afterwards.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel