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 the Arguments
As I said earlier that 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, 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
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
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells