In VBA, 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 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.
- First, declare a variable as range to store that cell address returned by the code.
- After that, refer to the range and use the “SpecialCells” method where you need to specify the “Type” argument with “xlCellTypeLastCell”.
- Next, set that special cell line of code to the variable that you have defined in the first step.
- From here, you need to use a VBA message box to get the address of the cells returns by the special cell method.
- In the end, use the address property with the variable to the get the address of the last type cell.
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 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 the SpecialCells method. Now, let’s see how you can use both arguments to select cells that have a formula, and that value that formula returns are 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 visible 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)
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel