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 paramater ??
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 equialant 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 




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext