Name related procedures in class Range (Excel VBA)

This page presents procedures from class Range related to the theme Name: ApplyNames, CreateNames and ListNames

ApplyNames

Applies names to the cells in the specified range.

You can use the Array function to create the list of names for the Names argument. If you want to apply names to the entire sheet, use Cells.ApplyNames. You cannot "unapply" names; to delete names, use the Delete method.

ApplyNames (Names, IgnoreRelativeAbsolute, UseRowColumnNames, OmitColumn, OmitRow, Order, AppendLast)

Cells.ApplyNames Names:=Array("Sales", "Profits")

Arguments

The following arguments are optional

Names (Names) - An array of the names to be applied. If this argument is omitted, all names on the sheet are applied to the range.

IgnoreRelativeAbsolute (Boolean) - True to replace references with names, regardless of the reference types of either the names or references. False to replace absolute references only with absolute names, relative references only with relative names, and mixed references only with mixed names. The default value is True.

UseRowColumnNames (Boolean) - True to use the names of row and column ranges that contain the specified range if names for the range cannot be found. False to ignore the OmitColumn and OmitRow arguments. The default value is True.

OmitColumn (Boolean) - True to replace the entire reference with the row-oriented name. The column-oriented name can be omitted only if the referenced cell is in the same column as the formula and is within a row-oriented named range. The default value is True.

OmitRow (Boolean) - True to replace the entire reference with the column-oriented name. The row-oriented name can be omitted only if the referenced cell is in the same row as the formula and is within a column-oriented named range. The default value is True.

Order (XlApplyNamesOrder) - Determines which range name is listed first when a cell reference is replaced by a row-oriented and column-oriented range name.

Possible Values are xlColumnThenRow - Columns listed before rows, xlRowThenColumn - Rows listed before columns.

AppendLast (Boolean) - True to replace the definitions of the names in Names and also replace the definitions of the last names that were defined. False to replace the definitions of the names in Names only. The default value is False.

CreateNames

Creates names in the specified range, based on text labels in the sheet.

If you don't specify one of Top, Left, Bottom, or Right, Microsoft Excel guesses the location of the text labels, based on the shape of the specified range.

CreateNames (Top, Left, Bottom, Right)

Set rangeToName = Worksheets("Sheet1").Range("A1:B3") 
rangeToName.CreateNames Left:=True

Arguments

The following arguments are optional

Top (Boolean) - True to create names by using labels in the top row. The default value is False.

Left (Boolean) - True to create names by using labels in the left column. The default value is False.

Bottom (Boolean) - True to create names by using labels in the bottom row. The default value is False.

Right (Boolean) - True to create names by using labels in the right column. The default value is False.

ListNames

Pastes a list of all nonhidden names onto the worksheet, beginning with the first cell in the range.

Use the Names property to return a collection of all the names on a worksheet.

ActiveCell.ListNames