Selecting Cells

cannot select a cell unless that particular worksheet is displayed/active


Selecting Individual Cells

If using the notation Range("An") is not convenient you can use Cells(rows,columns) instead.
The row and column indexes both start at 1 for Cells(row, column)
It is also possible to use the GoTo dialog box.

Range("B1").Select 

Cells(RowIndex, ColumnIndex).Select
Cells(1, 2).Select
Cells(1, "B").Select 'not guaranteed ???

Application.GoTo

This is comparable to the select method except the range is passed as a parameter
If the range is on another worksheet then that worksheet will be automatically selected.
GoTo is a method that causes Excel to select a range of cells and activate the corresponding workbook.
It takes an optional Object parameter (either String or Range).
It also takes an optional second Object parameter that can be set to True to indicate if you want Excel to scroll the window so that the selection is in the top-left corner.

Application.GoTo Range("B3") 
Application.GoTo Reference:=Range("A2")
Application.GoTo ("R3C2")
Application.GoTo ("Sheet1!R3C2")
Application.GoTo (Range("A2"),True)
Application.GoTo (Range("A2","B3"),True)

This is also has an optional scrolling parameter ??
The following line of code is not allowed.

Range(Cells(2,3)).Select 

Selecting a Range (or Multiple Cells)

Range("A1:D4").Select 
Range("A1", "D4").Select 'This is exactly the same as the above line
Range("A1,D4").Select 'This selects just the 2 cells

Range("A1", "G3").Select
Range("B1:C10").Select
Range("A1:B2, C3:D4").Select
Range("A1:B4", "D3:G6").Select
Range( Cells(2,3), Cells(5,6) ).Select
Worksheets("Sheet3").Range("A1:B10, C2:D40").Select
Application.Intersect(Range("A2"), Range("D10"))
Range("C1:C10 A6:E6").Select
Application.Union(Range("A1:G10"), Range("B6:D15"))

You can also use the following abbreviation although it is not recommended:

Range("A1:B3").Select 
[A1:B3].Select

Selecting the whole worksheet

ActiveSheet.Cells.Select 

Selecting a Different worksheet

The following line of code will not work unless Sheet1 is currently selected.

Worksheets("Sheet1").Range("A2").Select 

You must select the worksheet first and then select the range.


Selecting using the Current Selection

Using the Selection object performs an operation on the currently selected cells.
If a range of cells has not been selected prior to this command, then the active cell is used.

Selection 

Selecting using the Active Cell

The ActiveCell is often used and refers to the cell that is currently selected.
You can also easily obtain the cell address of the active cell.

ActiveCell.End(xlDirection.xlDown).Select 
Call MsgBox( ActiveCell.Column & ActiveCell.Row )

Selecting the CurrentRegion or UsedRange

The CurrentRegion property setting consists of a rectangular block of cells surrounded by one or more blank rows or columns.
The UsedRange is the range of all non-empty cells.

ActiveSheet.ActiveCell.CurrentRegion.Select 
ActiveSheet.UsedRange.Select

Selecting using Relative References

You can use the Range property of a Range object to create a relative reference to the Range object (e.g. Range("C3").Range("B2") = D4).
If you are using Range("A4".Cells(2,2)) to obtain a relative reference it is marginally faster to use Range("A4")(2,2).

Range("A2").End(xlDirection.xlDown).Select 
Range("A1:D10").Cells(6).Select

Selecting Rows and Columns

Range("C:C").Select 
Range("7:7").Select
Range("2:2,4:4,6:6").Select
Range("A;A,C;C,E;E").Select

Selecting all Non Blank

VBA Code > Special Cells


Range.SpecialCells Method

This method returns a range that represents all the cells that match a particular criteria

Range.SpecialCells(Type, Value) 

Type - The type of cells to select from the xlCellType enumeration.
Value - This is an additional argument that is required when Type is xlCellTypeConstants or xlCellTypeFormulas.
The default is to select all constants and formulas.

objBlankCells = Selection.SpecialCells(Type:=xlCellType.xlCellTypeBlanks) 

The following line selects the last "used" cell on the worksheet

ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeLastCell) 

xlCellTypeConstants or xlCellTypeFormulas.

Using this method will generate an error if there are no cells currently selected.
The following line creates a subset range containing all the cells that contain formulas.

objFormulaCells = Selection.SpecialCells(Type:=xlCellType.xlCellTypeFormulas, _ 
                                         Value:=xlSpecialCellsValue.xlNumbers)

The following line creates a subset range containing all the cells that contain constants

objConstantCells = Selection.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                                          Value:=xlSpecialCellsValue.xlNumbers)

The following line clears all constants from a worksheet

objWorksheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                                Value:=xlSpecialCellsValue.xlNumbers).ClearContents

Selecting all non blank cells on a sheet

Public Sub AllNonBlank() 
   Application.Union(ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeFormulas,
                                                    Value:=xlSpecialCellsValue.xlErrors + _
                                                           xlSpecialCellsValue.xlLogical + _
                                                           xlSpecialCellsValue.xlNumbers + _
                                                           xlSpecialCellsValue.xlTextValues), _
                     ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _
                                                    Value:=23)).Select
End Sub

Selecting all cells in a range that contain an error

This will select any cells that contain: #CALC!, #DIV/0, #FIELD!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #SPILL!, #VALUE!.

Public Sub CellsWithErrors 
   Range("A1:A16385").SpecialCells(xlCellType.xlCellTypeFormulas, xlSpecialCellsValue.xlErrors).Select
End Sub

KeyStroke Equivalents

The macro recorder does not record any keystrokes you use to select a range of cells.
This line of code is equivalent to pressing (Ctrl + Shift + 8).

ActiveCell.CurrentRegion.Select 

Multiple Selected Ranges

A Range object can comprise of multiple separate ranges.
Most properties and methods that refer to a range object take into account only the first rectangular area of the range.
You can use the Areas property to determine if a range contains multiple areas

If (Selection.Areas.Count > 1) Then 
End If

Excel will actually allow multiple selections to be identical.
You can hold down the Ctrl and click cell "A1" five time.
The selection will have five identical areas.


Protecting Cells

This just prevents the cells from being edited

Dim objRange As Excel.Range 

If (objRange.Locked = True) Then
'all cells in range are locked
End If

If (objRange.Locked = False) Then
'all cells in range are not locked
End If

If (objRange.Locked = null) Then
'cells in range have a combination of locked and unlocked
End If

GoTo Dialog

If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result.
These values can be added together to return more than one type.
The default is to select all constants or formulas, no matter what the type.


Comments

ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeComments).Select 
Selection.SpecialCells(xlCellType.xlCellTypeComments).Select

Constants

Selection.SpecialCells(xlCellType.xlCellType.Constants, 23).Select 
ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants, _
                               Value:=xlSpecialCellsValue.xlNumber).Select

Formulas

Selection.SpecialCells(xlCellType.xlCellTypeFormulas,3).Select 
ActiveSheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeFormulas, _
                               Value:=xlSpecialCellsValue.xlNumber).Select

Blanks

Selection.SpecialCells(xlCellType.xlCellTypeBlanks).Select 

CurrentRegion

Selection.CurrentRegion.Select 

CurrentArray

Selection.CurrentArray.Select 

Objects

ActiveSheet.DrawingObjects.Select 

Row Differences

Selection.RowDfferences(ActiveCell).Select 

Column Differences

Selection.ColumnDifferences(ActiveCell).Select 

Precedents

Selection.DirectPrecedents 

Dependents

Selection.DirectDependents 

Last Cell

Selection.SpecialCells(xlCellType.xlCellTypeLastCell).Select 

Visible Cells Only

Selection.SpecialCells(xlCellType.xlCellTypeVisible).Select 

Conditional Formatting

ActiveCell.SpecialCells(xlCellType.xlCellTypeAllFormatConditions).Select 

Data Validation

ActiveCell.SpecialCells(xlCellType.xlCellTypeAllValidation).Select 

Application.Intersect

Returns a Range object that represents the rectangular intersection of two or more ranges.


This example selects the intersection of two named ranges, rg1 and rg2, on Sheet1. If the ranges don't intersect, the example displays a message.

Worksheets("Sheet1").Activate 
Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
    MsgBox "Ranges do not intersect"
Else
    isect.Select
End If

Application.Union

Returns the union of two or more ranges.


This example fills the union of two named ranges, Range1 and Range2, with the formula =RAND().

Worksheets("Sheet1").Activate 
Set bigRange = Application.Union(Range("Range1"), Range("Range2"))
bigRange.Formula = "=RAND()"

Sub Testing_Selection() 
Dim lrowno As Long
Dim icolno As Integer
Dim objObject As Excel.Application
Dim iareatotal As Integer
Dim iareacount As Integer
Dim objFinalSelection As Excel.Range
Dim objUnionSelection As Excel.Range
Dim objcellfirst As Excel.Range
Dim objcellstartofrow As Excel.Range
Dim objSelection As Excel.Range

Set objObject = Application

    objObject.Range("C4:F15").Select
    objObject.Selection.FormulaArray = "=RAND()"

    Set objUnionSelection = objObject.Range("E10")
    Set objUnionSelection = Union(objUnionSelection, objObject.Range("F13"))
    objUnionSelection.Select

Set objSelection = objObject.Selection
iareatotal = objSelection.Areas.Count

For iareacount = 1 To iareatotal

lrowno = objSelection.Areas(iareacount).Row
icolno = objSelection.Areas(iareacount).Column

Set objcellfirst = objObject.Cells(lrowno, icolno)
Set objcellstartofrow = objSelection.Areas(iareacount).
            Offset(0, objObject.Cells(lrowno, icolno).CurrentRegion.Column - icolno)
Set objFinalSelection = objcellstartofrow.Resize(
             objSelection.Areas(iareacount).Rows.Count, objcellfirst.CurrentRegion.Columns.Count)

If iareacount = 1 Then
Set objUnionSelection = objFinalSelection
Else
Set objUnionSelection = Union(objUnionSelection, objFinalSelection)
End If

Next iareacount

objUnionSelection.Select
End Sub


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext