Excel VBA-range object

Source: http://blog.csdn.net/alexbnlee/article/details/6909689

Rows. Count displays the number of all rows
Columns. Count: displays the number of all columns.
Cells (rows. Count, 1). End (xlup). Row shows the row number of the first cell with a value from the number below.
Cells (1, columns. Count). End (xltoleft). column shows the column number of the first cell with a value on the right of the first row.
Cells (1, 1). borderaround xlcontinuous, xlthin adds an outer border range ("A1: B4") to cell A1. Borders. linestyle. = xlcontinuous adds a border to this area
Rows (1). autofit allocates the appropriate line spacing for the first row
Columns (1). autofit assigns an appropriate column width to the first column
Rows (1). delete Delete the first row
Columns (1). delete Delete the first column

Cells (1, 1). mergearea. Count calculates the total number of cells in the merged cells.
Cells (1, 1). mergearea. Rows. Count: calculates the number of rows in the merged cells.
Cells (1, 1). mergearea. Columns. Count: calculates the number of columns in the merged cells.
Cells (1, 1). mergecells returns Boolean, true, and false
The RND () function returns a random number less than 1 and greater than or equal to 0.
Cells (1, 1). Interior. colorindex = int (56 * RND () + 1) assign a random color to the cell.
Cells (1, 1). entirecolumn contains the column of this cell
Cells (1, 1). entirerow contains the row of this cell
Cells. Interior. colorindex = xlcolorindexnone all cells are five colors
Set RNG = application. Union (target. entirerow, target. entirecolumn) sets the merging area. Note that the Union method is under the Application
Set RNG = sheet1.usedrange. specialcells (xlcelltypeformulas) Find cells with formulas
Sheet1.range ("A: A"). clearcontents clears all content in the first column
Sheet1.range ("A: A"). Clear indicates everything in the first column, including values and formats.
For each RNG in sheet2.range ("A1: A40") traverses each cell in the area

Range ("A: A"). Replace "area", "city" Replacement
Range ("A: A"). replace what: = "zone", replacement: = "city" replace

Sheet1.range ("A1: G7"). Copy sheet2.range ("A1") Copy area, high width variation
Sheet1.range ("A1: G7"). Copy copy Region
Sheet3.range ("A1"). pastespecial xlpastecolumnwidth is pasted with the same width and must be set at the same height.

Range ("B1: B20"). validation. Add type: = xlvalidatelist, formula1: = "a, B, c, d, e, f, g" Data Validity

Range ("A1"). texttocolumns space: = trueNumber of spaces to separateString

Cells (3, 4). Distance from the top of the top cell to the top
Cells (3, 4). Left cell left distance to left
Cells (3) indicates the cells in the third column of the first row.
Activewindow. visiblerange. cells (3) indicates the cell in the third column of the first row of the current page
Cells (1, 1). Previous. Select move one position forward
Cells (1, 1). Next. Select move one position backward

Formula Calculation

Range ("C2"). formula = "= a2 * B2" simply multiply
Range ("C2"). Copy range ("C3: C" & R) copy the cell Formula
Cells (R + 1, 3). formula = "= sum (C2: C" & R & ")" addition formula, you can also use range
Range ("C2"). formular1c1 = "= RC [-2] * RC [-1]" is calculated by relative position.
Range ("C" & R + 1 ). formular1c1 = "= sum (R [-" & R-1 & "] C: R [-1] C)" Same as above, calculated by relative position. Note that, "-"