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
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