VBA Cells Range Methods and Property

In VBA, Cells and Range are two important objects that are used to manipulate data in Excel worksheets.

Cells object is used to refer to a single cell in a worksheet. It is usually used when you need to perform an operation on a single cell. For example, the following code sets the value of cell A1 to 1990:

Cells (1, 1). Value = 1990

Range object is used to refer to a group of cells in a worksheet. It is usually used when you need to perform an operation on a group of cells. For example, the following code sets the values of cells A1 to A10 to 20:

Range (\”A1:A10\”). Value = 20

You can also use the Range object to refer to a single cell. For example, the following code sets the value of cell A1 to 1990:

Range(\”A1\”). Value = 1990


VBA Select and Activate Methods

The VBA select and activate methods are used to manipulate objects in Excel, such as sheets, ranges, cells, shapes, charts, etc. Here are some differences and similarities between them:

  • Select can be used to select one or more objects at the same time, while activate can only select one object within a selection or by itself.
  • Select works only on the active worksheet, so you need to activate or select the worksheet before using select on a range of cells. Activate can be used to select workbooks, which do not have a select method.
  • Select and activate can perform the same action when there is only one object to select, but activate is more efficient and avoids unnecessary focus shifts that can affect performance and stability.

For example,

If we want to select cell D5 use below command
Range(“D5”). select
We can also select multiple range like to select range E1 to E5 use below code
Range (“E1:E5”). select

VBA Font Object

In VBA, there is a font object which you can use to change properties of the font from a cell, like, font color, font size, font type, and you can also apply bold and italic to the font.

VBA Cells Alignments

Cells alignment in VBA refers to the position of the text or data inside a cell or a range of cells. You can use the HorizontalAlignment and VerticalAlignment properties of the Range object to control the alignment of cells in VBA.

VBA Insert and Delete Rows/Columns

To insert a row or column in Excel using VBA, you can use the Insert method of the Range object. This method allows you to insert one or more rows or columns above or to the left of a specified range.