:: infotechGuyz.com ::
> For the IT community by the IT community

Excel 2010 Control Ranges Using Macro

 

A range can be a cell, row, column, or a grouping of any of these. The RANGE object is probably the most frequently used object in Excel VBA. Although a range can refer to any grouping of cells on a sheet, it can refer to only one sheet at a time. If you want to refer to ranges on multiple sheets, you must refer to each sheet separately.

The following are different ways of referring to ranges such as specifying a row or column, as well as how to manipulate cells based on the active cell and how to create a new range from overlapping ranges.

 

Excel object hierarchy:

Application > Workbook > Worksheet > Range

The Range object is a property of the Worksheet object, which means it requires sheet to be active or it must reference a worksheet. Both of the following lines mean the same thing if Worksheets(1) is the active sheet:

Range(“A1”)

Worksheets(1).Range(“A1”)

 

There are several ways to refer to a Range object. Range(“A1”) is the most identifiable because that is how the macro recorder refers to it. However, each of the following is equivalent when referring to a range:

Range(“D5”)

[D5]

Range(“B3”).Range(“C3”)

Cells(5,4)

Range(“A1”).Offset(4,3)

Range(“MyRange”) ‘assuming that D5 has

Name ‘of MyRange

 

 

Macro to Specify a Range

The Range property has two acceptable syntaxes. To specify a rectangular range in the first syntax, specify the complete range reference as you would in a formula in Excel:

Range(“A1:B5”).Select

 

In the alternative syntax, specify the upper-left corner and lower-right corner of the desired rectangular range. An equivalent statement might be this:

Range(“A1”, “B5”).Select

 

For either corner, you can substitute a named range, the Cells property, or the ActiveCell property. The following code selects the rectangular range from A1 to the active cell:

Range(“A1”, ActiveCell).Select

 

The following statement selects from the active cell to five rows below the active cell and two columns to the right:

Range(ActiveCell, ActiveCell.Offset(5, 2)).Select

 

 

Named Ranges

You probably have already used named ranges on your worksheets and in formulas. You can also use them in VBA.

Use the following code to refer to the range “MyRange” in Sheet1:

Worksheets(“Sheet1”).Range(“MyRange”)

Note that the name of the range is in quotes, which is different from the use of named ranges in formulas on the sheet itself. If you forget to put the name in quotes, Excel thinks you are referring to a variable in the program. One exception is if you use the shortcut syntax, in which quotes are not used.

 

Referencing Ranges in Different Sheets

Switching between sheets by activating the needed sheet can dramatically slow down your code. To avoid this slowdown, you can refer to a sheet that is not active by first referencing the Worksheet object:

Worksheets(“Sheet1”).Range(“A1”)

This line of code references Sheet1 of the active workbook even if Sheet2 is the active sheet. If you need to reference a range in another workbook, include the Workbook object, the Worksheet object, and then the Range object:

Workbooks(“InvoiceData.xlsx”).Worksheets(“Sheet1”).Range(“A1”)

Watch out if you use the Range property as an argument within another Range property. You must identify the range fully each time. For instance, suppose that Sheet1 is your active sheet and you need to total data from Sheet2:

WorksheetFunction.Sum(Worksheets(“Sheet2”).Range(Range(“A1”), Range(“A7”)))

This line does not work. Range(Range(“A1”), Range(“A7”)) refers to an extra range at the beginning of the code line. Excel does not assume that you want to carry the Worksheet object reference over to the other Range objects. So you could write this:

WorksheetFunction.Sum(Worksheets(“Sheet2”).Range(Worksheets(“Sheet2”). _

Range(“A1”), Worksheets(“Sheet2”).Range(“A7”)))

However, this is code is not only a long line, it is also difficult to read. The simpler way is to use With...End With:

With Worksheets(“Sheet2”)

WorksheetFunction.Sum(.Range(.Range(“A1”), .Range(“A7”)))

End With

Notice now that there is a .Range in your code, but without the preceding object reference. With Worksheets(“Sheet2”) implies that the object of the range is the worksheet.

 

 

Selecting Range by Cell Property

Cells property refers to all the cells of the specified range object, which can be a worksheet or a range of cells. For example, this line selects all the cells of the active sheet:

Cells.Select

Using the Cells property with the Range object might seem redundant:

Range(“A1:D5”).Cells

This line refers to the original Range object. However, the Cells property has an Item property that makes the Cells property very useful. The Item property makes it possible for you to refer to a specific cell relative to the Range object.

The syntax for using the Item property with the Cells property is as follows:

Cells.Item(Row,Column)

You must use a numeric value for Row, but you may use the numeric value or string value for Column. Both of the following lines refer to cell C5:

Cells.Item(5,”C”)

Cells.Item(5,3)

Because the Item property is the default property of the RANGE object, you can shorten these lines:

Cells(5,”C”)

Cells(5,3)

The ability to use numeric values for parameters is particularly useful if you need to loop through rows or columns. The macro recorder usually uses something like Range(“A1”). Select for a single cell and Range(“A1:C5”).Select for a range of cells.

If you are learning to code only from the recorder, you might be tempted to write code like this:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 to FinalRow

Range(“A” & i & “:E” & i).Font.Bold = True

Next i

This code loops through rows and bolds the cells in Columns A through E, though is awkward to read and write.

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 to FinalRow

Cells(i,”A”).Resize(,5).Font.Bold = True

Next i