Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.
Create Access and Copy Named Ranges
Contents
[
Hide
]
Introduction
Normally, column and row labels are used to refer to individual cells. It is possible to create descriptive names to represent cells, ranges of cells, formulas, or constant values. The word name may refer to a string of characters that represents a cell, a range of cells, a formula, or a constant value. Assigning a name to a range means that the range of cells can be referred to by its name. Use easy‑to‑understand names, such as Products, to refer to hard‑to‑understand ranges, such as Sales!C20:C30. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, you may use a name. Named ranges are among the most powerful features of Microsoft Excel, especially when used as the source range for list controls, pivot tables, charts, and so on.
Working with Named Range Using Microsoft Excel
Create Named Ranges
The following steps describe how to name a cell or range of cells using MS Excel. This method applies to Microsoft Office Excel 2003, Microsoft Excel 97, 2000, and 2002.
- Select the cell or range of cells that you want to name.
- Click the Name Box at the left end of the formula bar.
- Type the name for the cells.
- Press ENTER.
You cannot name a cell while you are changing the contents of the cell.
Working with Named Range Using Aspose.Cells for Python Excel Library
Here, we use the Aspose.Cells for Python via .NET API to perform the task. Aspose.Cells for Python via .NET provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a worksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a cells collection.
Create Named Range
It is possible to create a named range by calling the overloaded create_range method of the Cells collection. A typical version of the create_range method takes the following parameters:
- Name of the upper‑left cell in the range.
- Name of the lower‑right cell in the range.
When the create_range method is called, it returns the newly created range as an instance of the Range class. Use this Range object to configure the named range. For example, set the name of the range using the name property. The following example shows how to create a named range of cells that extends over B4:G14.
Input Data into the Cells in the Named Range
You can insert data into the individual cells of a range following the pattern
- C#:
Range[row, column] - VB:
Range(row, column)
Suppose you have a named range of cells that spans A1:C4. The matrix contains 4 × 3 = 12 cells. The individual range cells are arranged sequentially:
Range[0,0], Range[0,1], Range[0,2], Range[1,0], Range[1,1], Range[1,2], Range[2,0], Range[2,1], Range[2,2], Range[3,0], Range[3,1], Range[3,2].
Use the following properties to identify the cells in the range:
- FirstRow returns the index of the first row in the named range.
- FirstColumn returns the index of the first column in the named range.
- RowCount returns the total number of rows in the named range.
- ColumnCount returns the total number of columns in the named range.
The following example shows how to input some values into the cells of a specified range.
Identify Cells in the Named Range
If you have a named range that spans A1:C4, the matrix contains 4 × 3 = 12 cells. The individual range cells are arranged sequentially:
Range[0,0], Range[0,1], Range[0,2], Range[1,0], Range[1,1], Range[1,2], Range[2,0], Range[2,1], Range[2,2], Range[3,0], Range[3,1], Range[3,2].
Use the following properties to identify the cells in the range:
- FirstRow returns the index of the first row in the named range.
- FirstColumn returns the index of the first column in the named range.
- RowCount returns the total number of rows in the named range.
- ColumnCount returns the total number of columns in the named range.
The following example shows how to input some values into the cells of a specified range.
Access Named Ranges
Access a Specific Named Range
Call the Worksheet collection’s get_range_by_name method to get a range by the specified name. A typical get_range_by_name method takes the name of the named range and returns the specified named range as an instance of the Range class. The following example shows how to access a specified range by its name.
Access All the Named Ranges in a Spreadsheet
Call the Worksheet collection’s get_named_ranges method to get all named ranges in a spreadsheet. The get_named_ranges method returns an array of all named ranges in the Worksheet collection.
The following example shows how to access all the named ranges in a workbook.
Copy Named Ranges
Aspose.Cells for Python via .NET provides the Range.copy() method to copy a range of cells with formatting into another range.
The following example shows how to copy a source range of cells to another named range.