An Interactive Learning Tool

Proud to be a Teacher




Ranges in VBA

What is ranges?
A Range, refer to a cell or a range of cells.
One cell as range
Multiple cells, by row or by column or both or random cells, by using Ctrl+Click cell,

Example

A1, B1 or B5 (one Cell Range)
A1:A5, B1:B5 or A1:B5 (contiguous multiple cell range),
A1:B5 & C4 & D1 (non-contiguous Multiple cell range)
Note:
Every cell selected must be within same worksheet.

Name a Range of Cells

For Excel names that refer to a cell or a range of cells, you can use the names to quickly select the named range, and that makes navigation easier. Just select a range name from a drop down list, and you'll immediately go to that range.
In Excel, you can create names that refer to a single cell, a group of cells on the worksheet, a specific value, or a formula. After you define Excel names, you can use the names in a formula, instead of using a constant value or cell references. For example, a cell that contains the tax rate could be named SalesTax. Then, other cells could multiply sales amounts by the named range, SalesTax.

Name in Name Box

You can create a named range quickly by typing in the Name Box.
1. Select the cell(s) to be named
2. Click in the Name box, to the left of the formula bar
3. Type a valid one-word name for the list, e.g. Fruits.
4. Press the Enter key.

Ranges in VBA

In VBA ranges have been identified with statement Range()

Syntax

The basic syntax of the VBA range command is as follows:
Range(Cell 1, Cell 2) Where
Cell 1 (required) = The actual range/cell to be acted on. This should be a specific cell name (“A1”) or a range of cells (“A1: A10”). Cell 2 (optional) = The top-left or bottom-right of the cell range to be selected.
Range can accept not just cell names and ranges, but also strings:
Range(“Fruits”)
This will select cells which have the value of “Fruits”.
This is same as discussed for Name Range for spreadsheet above.


Terms of use Website Policy Contact us
© Copyrights ExcelYourVBA.com 2020