VBA Range Overview : Learn to Use One of the Most Commonly Utilized Worksheet Tools

Have you ever had to manually analyze or update data in Microsoft Excel? When you have a large amount of data that is difficult to manage and maintain, it’s way better to make a VBA program that will automatically manipulate the data according to your intended results. For instance, what if we want to reference a specific column, row or cell address, change cell attribute (font, height or width), or point to a particular cell within the worksheet? Range is a useful tool when dealing with complicated macro program to manipulate large amount of data.

In Microsoft Excel, Range is a group of cells selected in a worksheet. When you selected a number of cells, it will be highlighted by a black outline. By default, there is only cell highlighted when you open a workbook. It can be identified by cell references such as A1:B2 or name given to a selected group of cells.

Below are the most commonly used properties:

  1. Address – It returns a String value that represents the range of reference.
  2. AllowEdit – It returns a Boolean value that indicates if the range can be edited on a protected worksheet.
  3. Cells – It returns a Range object that represents the cells in the selected range.
  4. Columns – It returns a Range object that represents the columns in the selected range.
  5. ColumnWidth – It returns or sets the width of all columns in the selected range.
  6. Count – It returns a Long value that represents the number of objects.
  7. Font – It returns a Font object that represents the font of the selected object.
  8. Height – It returns or sets a Variant value that represents the height of the range.
  9. Offset – It returns a Range object that represents a range that’s offset from the selected range.
  10. Rows – It returns a Range object that represents the rows in the selected range.
  11. Row – It returns the number of the first row of the first area in the range.
  12. ShrinkToFit – It returns or sets a Variant value that indicates if text automatically shrinks to fit in the column width.
  13. Sort – It dynamically sort any selected columns.
  14. Text – It returns or sets the text for the selected object.
  15. Value – It returns or sets a Variant value that represents the value of the specified range.
  16. Width – It returns a Variant value that represents the width of the range.
  17. Worksheet – It returns a Worksheet object that represents the worksheet containing the specific range.

Syntax

expression .Range (Cell1, Cell2)

expression A variable that represents a Range object

Parameters

Name Required/Optional Data Type Description
Cell1 Required Variant The name of the range. This must be an A1-style reference in the language of the macro. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they?re ignored. You can use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the macro.
Cell2 Optional Variant The cell in the upper-left and lower-right corner of the range. Can be a Range object that contains a single cell, an entire column, or entire row, or it can be a string that names a single cell in the language of the macro.

Leave a Reply

Your email address will not be published. Required fields are marked *