Examples of assigning values to a range object in an Excel cell using VBA

Cell assignment example
-------------------

Example 1
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 2
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 3
Sub test3 ()
Msgbox "fill cells with formulas. In this example, the formula is a random number"
Range ("A1: H8"). formula = "= rand ()"
End sub

Example 4
Sub test4 ()
Worksheets (1). cells (1, 1). value = 24
Msgbox "the current cell A1 value is 24"
End sub

Example 5
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 6
Sub test6 ()
Msgbox "sets the formula in cell C5 ."
Worksheets (1). Range ("C5: C10"). cells (1, 1). formula = "= rand ()"
End sub

Cell reference example
-------------------

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.

Clear cell example
-------------------

Example 1
Clear the content in the cell (clearcontents method)
Sub testclearcontents ()
Msgbox "clear content in the specified cell area"
Worksheets (1). Range ("A1: H8"). clearcontents
End sub

Example 2
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 3
Clear comments in Cells)
Sub testclearcomments ()
Msgbox "clear comments in the specified cell area"
Worksheets (1). Range ("A1: H8"). clearcomments
End sub

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

Other examples
-------------------
Range and cells usage
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.

Select a cell area (select method)
Sub testselect ()
'Select cell region A1: D5
Worksheets ("sheet1"). Range ("A1: D5"). Select
End sub

Deviation from the selected region to another region (offset attribute)
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.

Select the number of rows and columns specified 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

resize
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: this example adjusts the size of the selected area, to add a row and a column.

select 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: Union (range1, range2 ,...) 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.

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

Select cells with specified conditions (specialcells method)
Sub selectspecialcells ()
Msgbox "select all formula cells in the current worksheet"
Activesheet. cells. specialcells (xlcelltypeformulas). Select
End sub

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

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