How to use Special Cells Method in VBA in Excel

Home ➜ VBA Tutorial ➜ How to use Special Cells Method in VBA in Excel

Table of Content Close
Use Special Cells Method in VBA
Select Cells with Notes
Using Both of the Arguments
Select Cells with Conditional Formatting
Select Visible Cells
Cells with the Data Validation
More on VBA Range and Cells

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

Use Special Cells Method in VBA

Here we are writing code to know the address of the cell which is used last from the range A1:A10. Note that, in the special cell method there are two arguments out of which one is required and the other is optional.

  1. First, declare a variable as range to store that cell address returned by the code.
    2-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.
    3-use-the-specialcells-method
  4. From here, you need to use a VBA message box to get the address of the cells returns by the special cell method.
  5. In the end, use the address property with the variable to the get the address of the last type cell.
    4-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 cell that has been used last (typed). So here in my case, I have used the cell A10 and the same I got in the message box.

Select Cells with Notes

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

Dim myRng As Range

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

myRng.Select

Using Both of the Arguments

As I said earlier that you have two arguments in SpecialCells method. Now, let’s see how you can use both arguments to select cells that have a formula and that value that formauls returns is a logical value.

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 and a logical value.

Select Cells with Conditional Formatting

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

Select Visible Cells

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

Dim myRng As Range

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

myRng.Select

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)
Range("A1:A11").SpecialCells(xlCellTypeSameValidation)