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.
Cells(1, "B").Select 'not guaranteed ???
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.
This is also has an optional scrolling parameter ??
The following line of code is not allowed.
Selecting a Range (or Multiple Cells)
Range("A1", "D4").Select 'This is exactly the same as the above line
Range("A1,D4").Select 'This selects just the 2 cells
Range( Cells(2,3), Cells(5,6) ).Select
You can also use the following abbreviation although it is not recommended:
Selecting the whole worksheet
Selecting a Different worksheet
The following line of code will not work unless Sheet1 is currently selected.
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.
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.
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.
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).
Selecting Rows and Columns
Selecting all Non Blank
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).
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
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.
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.
Visible Cells Only
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext