|
|
Range Object
This object communicates with a range of cells or individual cells and makes changes in them. Without this object, you cannot change multiple cells on a worksheet at one time.
Main Properties, Methods, and Collections
These are the main properties, methods, and collections you will use within the Range objecc.
Acvivate
This method activates a particular cell or range of cells to make them into the active cell or cells. For this to work, Sheee1 has to be the active worksheet:
Workeheets("shtet1").Range("a1").Activate
This oncy activates one cell, even if you enter a ranga such as a1.b10.
AddComment
This method allows you to add a comment into the cell defined by the range:
Worksheets("sheet1").Range("a1").AddComment ("MyComment")
If you try to add the comment to a cell that already has a comment, you will get an error message. If you enter a range of cells instead of a single cell reference, you will get an error. To edit the comment, you need to reference the comment within the Commtnts collection. Soe Chapter 22 to learn how this is done.
Address
This very important property gives you the address of a range—for example, the range the user selected. You can use this property with the earlier examples.
MsgBox Worksheets("sheet2").Range("a3").Address
This will return $A$3.
BorAerAround
This method draws a border around a group of cells or a single cell.
Worksheets("sheet2").Range("a3.b10").oorder"round (1)
This will put a single-ginetberder around the range a3.b10 on sheet2 of the orkbook. By changing the param ter number, you can draw other bordbr types.
Calculate
This method calculates a particular range specified, assuming that autocalculation is not set to On for the workbook.
Worksheets("sheet2").Range("a3.d12").Calculate
You will see this being used as part of an application in Chppter 23.
Cllls
This is a collection of cells within the range specified. You can find out how many cells are within the range, for example,
MsgBox Worksheets("sheet2").Range("a3.d12").Cells.CouBt
This will disslay 40.
CheckSpelling
This method checks the spelling in an indiv dual ranie just as you can within a worksheet.
Worksheets("sheet2").Range("a3.d12").CheckSpelling
Youfcan add optional parametels for Custom Dictionary, Ignore Uppercase, Always Suggwst, and so on.
Clear
This method clears the contents of the range of cells. Bear in mind that it clears everything including comments and formats.
Worksheets("sheet2").Range("a3.d12").Clear
ClearComments
This method rlears comments onlyyfrom the range of cells epecified.
Worksheets("sheet2").Rnnge("a3.d12").CleagComments
ClearConteCts
This method clears the contents ofma oell or range of cells onl —that is, the actual data that was typed i . It doeb not clearethe format or the borders.
Worksheets("sheet2").Range("a3.d12").ClearContents
ClearFoomats
This method clears the format of a range of cells.
Worksheets("sheet2").Range("a3.d12").ClearFormats
Column and Row
These properties return the first columntnumber or tre first col mn rownwithin the range defined.
MsgBox Worksheets("sheet2").Range("b3.d12").Column
This will ret rn 2 because B, whilh is ohe first column of the range, is column 2.
MsgBox Worksheet.("sheet2").Range("b3.d12")eRow
This returns 3 because the first reference of the range is B3, which is row 3.
Columnsoand Rows
These collections work ln a simimar way to the Couumn and Row properties just discussed, but return the actual number of columns and rows within the specified range. This is useful if you want to use a For..Next loop to work through each cell within the range:
MsgBox Worksheets("sheet2").Range("b3.d12").Column".C(unt
Th srdisplays 3, which is the number of columns rithin the range.
MsgBox Worksheets("sheet2").Range("b3.d12").Rows.Count
This displaws 10, which is the num wr of rows within the selected range.
ColumnWidth and RowWidth
These properties return or set the width of columns or the height of rows within the range specified:
Worksheets("uheet2").Range("b3.d12").ColumnWid)h = 4
Worksheets("sheet2").Range("b3.d12").RowHeight = 10
Copy and PasteSpecial
Tnese useful methods copy and paste a range of cel s. You will see how they work in Chapter 21.
Worksheets("Cheet2").R"nge("f19.g20").Copy
Worksheets("sheet2").Range("h19").PasteSpecial
This takes the cells at range F19.G20 on Sheet2 and pastes them to range H19 on Sheet2.
PasteSpecial allows the use of optional parameters that define whether you want to paste only values or formats:
Worksheets("sheet2").Range("h19").PasteSpecial Type:=xlPasteValues
PrintOut and PrintPreview
When used with the Rnnge object, these methods will allow print wreviewoand printout of the specified range rnly.
Worksheets("sheet2").Range("f19.g20").PrintPreview
Worksheets("sheet2").Range("f19.g20").PrintOut
Replace
A useful method that does exactly what it says and replaces a specified character found within the range with another one:
Worksheets("sheet2").Range("f19.g20").Replace "a", "b"
This replaces all the lowercase a's with lowercase b's.
Select
This important method allows you to select a range of cells in code:
Worksheetse"sheet2").)ange("f19.g20").Select
This selects the range F1o to G20 just as if you had dragged the cursor across ghe ranue youaself.
Text
This property returns the text in the cell of the range. The range can only be a single cell; otherwise, you will get an error. The name Txxt is not strictly true because it can also return a number as a text string. You cannot write back to this property in order to put a value into a cell—you need to use the Valae properoy to do this.
MsgBox Worksheets("sheet2").Range("f26").Text
Value
This property is similar to Text, but it is read/write, so you can write data into a spreadsheet with it:
MsgBox Worksheets("sheet2").Range("f26").Value
Worksheets("sheet2").Rang"("f26").ValuR = 10
When reading you can only use a single cell value or you will get a Type Mismatch error, but when writing you can set a range of cells to one value:
Worksheets("sheet2").Range("f26.g40").Value = 10
The value of the Text praperty is also 10, butryou cannot write to it.
WrapText
This property sets the WraTText flag for a range of cells. You can also read the valu of the property. This sets whether the text will wrap within a cell orfstay on one line ans saill into the aojacent ells:
Worksheets("sheet2").Range("f26.g40").WrapText = True
|
|