Example of basic operation on a range object (1)

The range object may be VBA. Code The most common object. A range object can be a region consisting of a cell, a cell area, a row, a column, or multiple consecutive or non-consecutive areas. The following describes some attributes and methods of the range object.
--------------------------------------
[Example 05-01] assign a value to a cell
[Example 05-01-01]
Sub test1 ()
Worksheets ("sheet1"). Range ("A5"). value = 22
Msgbox "the value in cell A5 in worksheet sheet1 is "_
& Worksheets ("sheet1"). Range ("A5"). Value
End sub
[Example 05-01-02]
Sub Test2 ()
Worksheets ("sheet1"). Range ("A1"). value = _
Worksheets ("sheet1"). Range ("A5"). Value
Msgbox "the value in cell A1 is "&_
Worksheets ("sheet1"). Range ("A5"). Value
End sub
[Example 05-01-03]
Sub test3 ()
Msgbox "fill cells with formulas. In this example, the formula is a random number"
Range ("A1: H8"). formula = "= rand ()"
End sub
[Example 05-01-04]
Sub test4 ()
Worksheets (1). cells (1, 1). value = 24
Msgbox "the current cell A1 value is 24"
End sub
[Example 05-01-05]
Sub test5 ()
Msgbox "set the formula for cells and calculate the sum of the cells B2 and B5"
Activesheet. cells (2, 1). formula = "= sum (B1: B5 )"
End sub
[Example 05-01-06]
Sub test6 ()
Msgbox "sets the formula in cell C5 ."
Worksheets (1). Range ("C5: C10"). cells (1, 1). formula = "= rand ()"
End sub
--------------------------------------
[Example 05-02] referencing a cell
Sub random ()
Dim myrange as range
'Set the reference to the cell area
Set myrange = worksheets ("sheet1"). Range ("A1: D5 ")
'Operation on the range object
Myrange. formula = "= rand ()"
Myrange. Font. Bold = true
End sub
Example: you can set a range object variable to reference the cell area and then operate on the cell area represented by the variable.
--------------------------------------
[Example 05-03] clear cells
[Example 05-03-01] Clear the content in a cell (clearcontents method)
Sub testclearcontents ()
Msgbox "clear content in the specified cell area"
Worksheets (1). Range ("A1: H8"). clearcontents
End sub
[Example 05-03-02] clear format in cells (clearformats method)
Sub testclearformats ()
Msgbox "clearing the format in the specified cell area"
Worksheets (1). Range ("A1: H8"). clearformats
End sub
[Example 05-03-03] clear comments in cells (clearcomments method)
Sub testclearcomments ()
Msgbox "clear comments in the specified cell area"
Worksheets (1). Range ("A1: H8"). clearcomments
End sub
[Example 05-03-04] Clear all cells, including content, format, and annotation (clear method)
Sub testclear ()
Msgbox "Clear the specified cell area completely"
Worksheets (1). Range ("A1: H8"). Clear
End sub
--------------------------------------
[Example 05-04] range and cells
Sub test ()
'Set the border line style of cell area A1: j10
With worksheets (1)
. Range (. cells (1, 1 ),_
. Cells (10, 10). Borders. linestyle = xlthick
End
End sub
Example: A range object is returned for available range (cell1, cell2). cell1 and cell2 are the range objects at the specified start and end positions.
--------------------------------------
[Example 05-05] Select a cell area (select method)
Sub testselect ()
'Select cell region A1: D5
Worksheets ("sheet1"). Range ("A1: D5"). Select
End sub
--------------------------------------
[Example 05-06] deviation from the selected region to another region (offset attribute)
[Example 05-06-01]
Sub testoffset ()
Worksheets ("sheet1"). Activate
Selection. offset (3, 1). Select
End sub
For example, offset (row, column) (where row and column are the row offset and column offset) can be used to return the region at the specified offset relative to the other region. In the preceding example, the cells in the upper-left corner of the selected area are selected, and the cells in the right column are selected.
[Example 05-06-02] Select a cell with the specified number of rows and columns from the current cell
Sub activecelloffice ()
Msgbox "displays the values of cells in the 3rd columns and 2nd rows from the current cell"
Msgbox activecell. offset (3, 2). Value
End sub
--------------------------------------
[Example 05-07] resize a region (resize attribute)
Sub resizerange ()
Dim numrows as integer, numcolumns as integer
Worksheets ("sheet1"). Activate
Numrows = selection. Rows. Count
Numcolumns = selection. Columns. Count
Selection. Resize (numrows + 1, numcolumns + 1). Select
End sub
Example: In this example, adjust the size of the selected region to add a row and a column.
--------------------------------------
[Example 05-08] selecting multiple regions (Union method)
Sub testunion ()
Dim rng1 as range, rng2 as range, mymultiarearange as range
Worksheets ("sheet1"). Activate
Set rng1 = range ("A1: B2 ")
Set rng2 = range ("C3: D4 ")
Set mymultiarearange = Union (rng1, rng2)
Mymultiarearange. Select
End sub
Example Description: Union (range1, range2,...) can be used to return multiple areas, that is, these areas are composed of two or more consecutive cells. In the preceding example, an object defined by the combination of cell A1: B2 and C3: D4 is created, and the definition area is selected.
--------------------------------------
[Example 05-09] activate a cell in the selected region
Sub activaterange ()
Msgbox "select cell area B2: D6 and select C4"
Activesheet. Range ("B3: D6"). Select
Range ("C5"). Activate
End sub
--------------------------------------
[Example 05-10] selecting cells with specified conditions (specialcells method)
Sub selectspecialcells ()
Msgbox "select all formula cells in the current worksheet"
Activesheet. cells. specialcells (xlcelltypeformulas). Select
End sub
--------------------------------------
[Example 05-11] Select a rectangular area (currentregion attribute)
'Select the rectangular area that contains the current cell.
'The area is surrounded by blank rows and blank columns
Sub selectcurrentregion ()
Msgbox "select the rectangular area containing the current cell"
Activecell. currentregion. Select
End sub
--------------------------------------
[Example 05-12] Select cells used in the current Worksheet (usedrange attribute)
'Select the cells used in the current worksheet.
Sub selectusedrange ()
Msgbox "select the cells used in the current worksheet "_
& Vbcrlf & "and display its address"
Activesheet. usedrange. Select
Msgbox activesheet. usedrange. Address
End sub
--------------------------------------
[Example 05-13] selecting the Edge Cell (end attribute)
'Select the bottom Cell
Sub selectendcell ()
Msgbox "select the bottom cell in the current cell area"
Activecell. End (xldown). Select
End sub
Example: You can change the xldown parameter to select the leftmost, rightmost, and rightmost cells.
--------------------------------------
[Example 05-14] set the values of the previous and next cells of the current cell
Sub setcellvalue ()
Msgbox "sets the cell value in front of the current cell to" "My front cell" "& vbcrlf _
& "Set the cell value to" "My cell """
Activecell. Previous. value = "cell above me"
Activecell. Next. value = "cell after me"
End sub
--------------------------------------
[Example 05-15] Check whether the formula (hasformula attribute) exists in the selected cell area)
Sub ifhasformula ()
If selection. hasformula = true then
Msgbox "formulas are available in the selected cells"
Else
Msgbox "in selected cells, some cells do not have a formula"
End if
End sub
--------------------------------------
[Example 05-16] formula cell operation
[Example 05-16-01] obtain a cell directly related to the result Cell
Sub calrelationcell ()
Msgbox "selecting cells related to the calculation result of the current cell"
Activecell. directprecedents. Select
End sub
[Example 05-16-02] tracing formula Cell
Sub cal1 ()
Msgbox "select all cells related to the calculated result cell"
Activecell. Precedents. Select
End sub
Sub trackcell ()
Msgbox "Operation Result cell tracing"
Activecell. showprecedents
End sub
Sub deltrack ()
Msgbox "delete tracing line"
Activecell. showprecedents remove: = true
End sub
--------------------------------------
[Example 05-17] copying a cell (copy method)
Sub copyrange ()
Msgbox "after the formula is written to cell B7, copy the content of B7 to C7: D7"
Range ("B7"). formula = "= sum (B3: B6 )"
Range ("B7"). Copy destination: = range ("C7: D7 ")
End sub
--------------------------------------
[Example 05-18] Getting the row and column values of cells (row and column attributes)
Sub rangeposition ()
Msgbox "display the row and column values of the selected cell area"
Msgbox "no." & selection. Row & "row" & selection. Column & "column"
End sub
--------------------------------------
[Example 05-19] obtain the number of cells and the number of rows and columns in the cell area (rows attribute, columns attribute, and count attribute)
Sub getrowcolumnnum ()
Msgbox "displays the number of cells, number of rows, and number of columns in the selected cell area"
Msgbox "the number of cells in the cell area is:" & selection. Count
Msgbox "the number of rows in the cell area is:" & selection. Rows. Count
Msgbox "the number of columns in the cell area is:" & selection. Columns. Count
End sub
--------------------------------------
[Example 05-20] setting the text format in Cells
[Example 05-20-01] Alignment text
Sub horizontalalign ()
Msgbox "align left and right text in the selected cell area to Center"
Selection. horizontalalignment = xlhaligncenter
End sub
Sub verticalalign ()
Msgbox "align and align the text in the selected cell area to Center"
Selection. rowheight = 36
Selection. verticalignment = xlvaligncenter
End sub
[Example 05-20-02] indent text (insertindent method)
Sub indent ()
Msgbox "adding 1 to the text in the selected cell area"
Selection. insertindent 1
Msgbox "Restore the Contraction Value"
Selection. insertindent-1
End sub
[Example 05-20-03] setting the orientation attribute)
Sub changeorientation ()
Msgbox "rotating the text in the selected cell 45 degrees clockwise"
Selection. Orientation = 45
Msgbox "Change text from landscape to portrait"
Selection. Orientation = xlvertical
Msgbox "Restore original value in the text direction"
Selection. Orientation = xlhorizontal
End sub
[Example 05-20-04] automatic line feed (wraptext attribute)
Sub changerow ()
Dim I
Msgbox "set the selected cell to wrap automatically"
I = selection. wraptext
Selection. wraptext = true
Msgbox "Restore to original state"
Selection. wraptext = I
End sub
[Example 05-20-05] scale out text that is longer than the cell column to a shrinktofit attribute)
Sub autofit ()
Dim I
Msgbox "scale the text longer than the column width to the same as the column width"
I = selection. shrinktofit
Selection. shrinktofit = true
Msgbox "Restore to original state"
Selection. shrinktofit = I
End sub
--------------------------------------
[Example 05-21] set the condition format (formatconditions attribute)
Sub formatconditions ()
Msgbox "red text in cells with a cell value less than 10 in the selected cell area"
Selection. formatconditions. Add type: = xlcellvalue ,_
Operator: = xllessequal, formula1: = "10"
Selection. formatconditions (1). Font. colorindex = 3
Msgbox "Restore to original state"
Selection. formatconditions (1). Font. colorindex = xlautomatic
End sub
--------------------------------------
[Example 05-22] Insert annotation (addcomment method)
Sub entercomment ()
Msgbox "entering comments in current cell"
Activecell. addcomment ("hello ")
Activecell. Comment. Visible = true
End sub
--------------------------------------
[Example 05-23] Hiding/displaying cell comments
Sub cellcomment ()
Msgbox "switching the display and hide status of the current cell annotation"
Activecell. Comment. Visible = Not (activecell. Comment. Visible)
End sub
--------------------------------------
[Example 05-24] changing the color of the selected cell
Sub changecolor ()
Dim Iro as integer
Msgbox "change the color of the selected cell to red"
IRO = selection. Interior. colorindex
Selection. Interior. colorindex = 3
Msgbox "change the color of the selected cell to blue"
Selection. Interior. Color = RGB (0, 0,255)
Msgbox "Restore to original state"
Selection. Interior. colorindex = Iro
End sub
--------------------------------------
[Example 05-25] changing the cell pattern
Sub changepattern ()
Dim P, PC, I
Msgbox "change the pattern of the selected cell in the order of regular pattern values"
P = selection. Interior. Pattern
PC = selection. Interior. patterncolorindex
For I = 9 to 16
With selection. Interior
. Pattern = I
. Patterncolor = RGB (255, 0, 0)
End
Msgbox "Constant Value" & I
Next I
Msgbox "Restore to original state"
Selection. Interior. pattern = P
Selection. Interior. patterncolorindex = pc
End sub
--------------------------------------
[Example 05-26] merge Cells
Sub mergecells ()
Msgbox "merge cells A2: C2 and set the text to center and align"
Range ("A2: C2"). Select
With Selection
. Mergecells = true
. Horizontalalignment = xlcenter
End
End sub
--------------------------------------
[Example 05-27] Restrict the range of cell movement
Sub scrollarea1 ()
Msgbox "limits the cell's moving range to the cell area B2: D6"
Activesheet. scrollarea = "B2: D6"
End sub
Sub scrollarea2 ()
Msgbox "lift the moving range limit"
Activesheet. scrollarea = ""
End sub
--------------------------------------
[Example 05-28] Getting the location of a cell (address attribute)
Sub getaddress ()
Msgbox "show the address of the selected cell area"
Msgbox "absolute address:" & selection. Address
Msgbox "absolute address of the row:" & selection. Address (rowabsolute: = false)
Msgbox "absolute column address:" & selection. Address (columnabsolute: = false)
Msgbox "displayed as r1c1:" & selection. Address (referencestyle: = xlr1c1)
Msgbox "relative address:" & selection. Address (false, false)
End sub
--------------------------------------
[Example 05-29] deleting a cell area (delete method)
Sub deleterange ()
Msgbox "Move the right cell to the left after deleting cell area C2: D6"
Activesheet. Range ("C2: D6"). Delete (xlshifttoleft)
End sub

 

Summary
This section briefly summarizes some common attributes and methods of the range object.
1. Activate and select
Test the following process:
Sub selectandactivate ()
Range ("B3: E10"). Select
Range ("C5"). Activate
End sub
The result is shown in:

Figure 05-01: select and activate
Selection indicates cell area B3: E10, while activecell indicates cell C5. activecell indicates a single cell, and selection indicates a single cell or cell area.
2. Range attribute
You can use the range attribute of the Application object to reference the range object, as shown in figure
Application. Range ("B2") 'indicates the cell B2 in the current worksheet.
If you reference cells in the current worksheet, you can ignore the previous application object.
Range ("A1: D10") 'indicates the cell area A1: D10 in the current worksheet.
Range ("A1: A10, C1: C10, E1: E10") 'indicates the cells that are composed of three consecutive areas in the current worksheet.
The range attribute also accepts two parameters pointing to the diagonal corner of the cell area, such:
Range ("A1", "D10") 'indicates the cell area A1: D10
Of course, the range attribute also accepts the cell region name, for example:
Range ("data") 'indicates the data region named data.
The parameter of the range attribute can be an object or a string, for example:
Range ("A1", range ("lastcell "))
3. Cell reference shortcut
Square brackets can be added to both sides of the reference area to quickly reference the cell area, such:
[B2]
[A1: D10]
[A1: A10, C1: C10, E1: E10]
[Data]
However, it references absolute regions.
4. Cells attributes
You can use the cells property to reference a range object. For example:
Activesheet. Cells
Application. Cells reference all cells in the current Worksheet
Cell (2, 2)
Cell (2, "B") 'References cell B2
Range (cells (), cells () 'References cell area A1: E10
It is convenient to use the cells attribute when you want to loop in a cell area.
You can also use the cells attribute for relative reference, such:
Range ("D10: G20"). cells (2, 3) 'indicates the cell that references the 2nd rows and 3rd columns of the cell area D10: G20, that is, the cell F11.
You can also use the statement range ("D10"). cells (2, 3) to achieve the same reference effect.
5. offset attribute
The offset attribute is offset based on the given parameters of the current cell. Unlike the cells attribute, the offset attribute is based on 0, that is, the base cell is 0, for example:
Range ("A10"). cells () and range ("A10"). offset () indicate cell A10
The offset attribute is useful when you want to reference a cell area of the same size as the base cell area.
6. Resize attributes
You can use the resize attribute to obtain the area with the specified size relative to the cell in the upper left corner of the original cell area.
7. specialcells Method
The specialcells method corresponds to the "positioning condition" dialog box, as shown in 05-02:
Figure 05-02: "positioning condition" dialog box
8. currentregion attributes
You can use the currentregion attribute to select the region where the current cell is located, that is, the surrounding area is a rectangle surrounded by empty rows and empty columns. It is equivalent to the "Ctrl + Shift + *" shortcut key.
9. End attributes
The end attribute represents the operation equivalent to the "Ctrl + direction arrow" operation. The constants xlup, xldown, xltoleft, and xltoright represent the upper, lower, left, and right arrows respectively.
10. Columns attributes and rows attributes
The columns attribute and the rows attribute return all columns and all rows in the cell area.
11. areas set
When columns and rows attributes are used in multiple discontinuous cell areas, only rows or columns in the first region are returned, for example:
Range ("A1: B5, C6: D10, E11: F15"). Rows. Count
5 is returned.
In this case, the areas set should be used to return the address of each block in the region, for example:
For each RNG in range ("A1: B5, C6: D10, E11: F15"). Areas
Msgbox RNG. Address
Next RNG
12. Union and intersect Methods
Use the Union method when you want to generate a cell area from two or more cell areas. Use the intersect method when you find the cell areas shared by two or more cell areas.

 

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/cnjack/archive/2009/10/26/4729917.aspx