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


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.


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 

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