Refer To Excel Cell, Offset, Resize - Code VBA

Refer to a cell or group of cells

This page handles how to refer to ranges on a worksheet. Next to refer directly you can Refer with ActiveCell or use Offset or Resize.

Specify the range directly.

A common task when using VBA is to specify a cell (or range of cells) and then do something with it, such as enter a formula or change its value.You can usually do this in one statement that identifies the range and also changes a property or applies a method.

Here's an example that assigns the value 10 to range A1:C12 on Sheet1 in a workbook named MyBook:

Workbooks("MyBook").Sheets("Sheet1").Range("A1:C12").Value = 10

This statement can be simplified as:

Range("A1:C12").Value = 10

To select a cell in Excel, you have two basic methods: Range and Cells:

Range ("A1").Select 'Here the cell is referred by its name

Range("RangeName").Select.

Range works well for hard-coded cells.

Cells(2, 4).Select 'Selects Row 2, Column 4, i.e. cell D2.

Cells works best with calculated cells, especially when you couple it with a loop:

For i = 1 To 10 Cells(i, 1).Value = i ' fill A1 through A10 with the value of i Next i

Referring with ActiveCells

It is also possible to use ActiveCell for referring. ActiveCell is the cell which is highligted in the Excel sheet.

Range(ActiveCell, "E5").Select 'This selects a block from whereever the Active Cell to E5

Offset

The Offset property is used with the Range property. You specify a new location, based on one or more cells you also specify.

Range("A1").Offset(RowOffSet:=1, ColumnOffset:=1).Select ' move from cell A1 one row and one column, so you get B2 .

Selecting on the left goes by negative numbers

Range("B2").Offset(-1, -1 ).Select

You can leave out one of the arguments

Range("A1").Offset(, 1 ).Select ' means Select B1, mind the comma

Range("A1").Offset(1 ).Select ' means Select A2,

Example: Calculating the sum of values in a Range finds only 1 value.

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues) Rng.Offset(, 1).Value = "LOW"

You can loop through a list more efficiently with Offset. It is easier to program and faster to execute.

Resize

The Resize property enables you to change the size of a range based on a chosen starting position. You can create a new range as you need it.

Range.Resize(RowSize, ColumnSize)

To create a range B3:D12, use this:

Range("B3").Resize(RowSize:=10, ColumnSize:=3) or, simpler: Range("B3").Resize(10, 3).Select