It is very common when using the macro recorder that there are .Select methods in the saved code, next to the Range object.
.Select is used to select one or more elements of Excel (as can be done by using the mouse) allowing further manipulation of the selection.
Selecting cells with the mouse:
Selecting cells with VBA:
'Range([cell1],[cell2]) Range(Cells(1, 1), Cells(9, 5)).Select Range("A1", "E9").Select Range("A1:E9").Select
All the above lines of code do the same thing: they select the range from "A1" to "E9".
If a region is populated by data with no empty cells, an option for an automatic selection is the CurrentRegion property.
CurrentRegion will select starting from a Range all the area populated with data.
Be aware that there are no gaps between values, as CurrentRegion will map the region through adjoining cells (horizontal, vertical and diagonal).With all the adjacent data
Not all adjacent data
"C4" is not selected because it is not immediately adjacent to any filled cells.
The ActiveCell property brings up the active cell of the worksheet. In the case of a selection, it is the only cell that stays white.
A worksheet has only one active cell.
Range("B2:C4").Select ActiveCell.Value = "Active"
Usually the ActiveCell property is assigned to the first cell (top left) of the Range, being different from this when the selection is made manually by the user (without macros).
The AtiveCell property is used in more advanced commands, such as .
After selecting the desired cells, we can use Selection to refer to it and thus make changes:
Range("A1:D7").Select Selection = 7
Selection also accepts methods and properties (which vary according to what was selected).
Selection.ClearContents 'Deletes only the contents of the selection Selection.Interior.Color = RGB(255, 255, 0) 'Adds background color to the selection
As in this case a cell range has been selected, the Selection will behave similarly to a Range. Therefore, Range should also accept the .Interior.Color property.
RGB (Red Green Blue) is a highly diffused color system that is used in a number of applications and languages. The input values for each color, in the example case, range from 0 to 255.
If there is a need to replicate a formula to an entire selection, you can use the .FillDown method
Before the FillDown
After the FillDown
.FillDown is a method applicable to Range. Since the Selection was done in a range of cells (equivalent to a Range), the method will be accepted.
.FillDown replicates the Range/Selection formula of the first line, regardless of which ActiveCell is selected.
.FillDown can be used at intervals greater than one column (E.g. Range("B1:C2").FillDown will replicate the formulas of B1 and C1 to B2 and C2 respectively).
You can select one or multiple rows or columns with VBA.
The selection will always refer to the last command executed with Select.
To insert a row, simply use the Insert method.
Range("A7").EntireRow.Insert 'In this case, the content of the seventh row will be shifted downward
To delete a row, just use the Delete method.
Range("A7").EntireRow.Delete 'In this case, the content of the eighth row will be moved to the seventh
Just like with the EntireRow and EntireColumn property, you can use Rows and Columns to select a row or column.
To hide rows:
Range("A1:C3").Rows.Hidden = True
In the example, rows 1 to 3 of the worksheet were hidden.
Row and Column are properties that are often used to obtain the numerical address of the first row or first column of a selection or a specific cell.
Range("A3:H30").Row 'Referring to the row; returns 3 Range("B3").Column 'Referring to the column; returns 2
The results of Row and Column are often used in loops or resizing.
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2019 SuperExcelVBA | ABOUT
Thank you for contributing. A message was sent reporting your comment.