Skip to main content

Range

APIDescriptionExample
Worksheet.RangeFunction that specifies a specific range of the designated sheetlet sheet = instance.Application.ActiveSheet;

let cell = sheet.Range("A1");
let range = sheet.Range("A1:B2");
Range.CellsProperty that returns a range object that manages the specified range by cell unitslet range = instance.Application.ActiveSheet.UsedRange; let cells = range.Cells;
Range.ColumnProperty that returns the column number of the top-left cell of the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

console.log(range.Column);
Range.ColumnsProperty that returns a range object that manages the specified range by column unitslet range = instance.Application.ActiveSheet.UsedRange;

let columns = range.Columns;
Range.CountProperty that returns the number of cells in the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

console.log(range.Count);
Range.EntireColumnProperty that returns a range object that manages the entire column of the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

let entireColumn = range.EntireColumn;
Range.EntireRowProperty that returns a range object that manages the entire row of the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

let entireRow = range.EntireRow;
Range.HasArrayProperty that returns whether the specified range is part of an array formula
cf) Works normally when range is a single cell (A1)		let range = instance.Application.ActiveSheet.UsedRange;

console.log(range.HasArray);
Range.HasFormulaProperty that returns whether the specified range contains formulas
cf) Works normally when range is a single cell (A1)		let range = instance.Application.ActiveSheet.UsedRange;

console.log(range.HasFormula);
Range.HiddenProperty that returns or changes the hidden state when the specified range consists only of columns or rowslet columns = instance.Application.ActiveSheet.Columns;

console.log(columns.Item("A").Hidden);
columns.Item("A").Hidden = true;
Range.LeftProperty that returns the top-left X coordinate position of the specified range in points (pt)let range = instance.Application.ActiveSheet.UsedRange;

console.log(range.Left);
Range.NextProperty that returns a range object corresponding to the next cell of the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

let nextCell = range.Next;
Range.PreviousProperty that returns a range object corresponding to the previous cell of the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

let previousCell = range.Previous;
Range.RowProperty that returns the row number of the top-left cell of the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

console.log(range.Row);
Range.RowsProperty that returns a range object that manages the specified range by row unitslet range = instance.Application.ActiveSheet.UsedRange;

let rows = range.Rows;
Range.ShrinkToFitProperty that returns or changes whether the specified range automatically adjusts column width to fit datalet range = instance.Application.ActiveSheet.UsedRange;

console.log(range.ShrinkToFit);
range.ShrinkToFit = true;
Range.TopProperty that returns the top-left Y coordinate position of the specified range in points (pt)let range = instance.Application.ActiveSheet.UsedRange;

console.log(range.Top);
Range.AddressFunction that returns the specified range as a stringlet range = instance.Application.ActiveSheet.UsedRange;

console.log(range.Address());
Range.EndFunction that returns a range object corresponding to the end cell of the column or row of the top-left cell of the specified rangelet range = instance.Application.ActiveSheet.Range("D3:F6");

let d1Cell = range.End(instance.Enums.XlDirection.xlUp);
let a3Cell = range.End(instance.Enums.XlDirection.xlToLeft);
Range.ItemFunction that returns a range object corresponding to a cell at a specific index within the specified rangelet range = instance.Application.ActiveSheet.Range("D3:F6");

let d3Cell = range.Item(1);
let e3Cell = range.Item(2);
let e4Cell = range.Item(2, 2);
Range.OffsetFunction that returns a range object at a position moved by a specific offset from the specified rangelet range = instance.Application.ActiveSheet.Range("D3:F6");

let d4f7Range = range.Offset(1);
let d2f5Range = range.Offset(-1);
let e4g7Range = range.Offset(1, 1);
Range.ResizeFunction that returns a changed range object by modifying the size of the specified rangelet range = instance.Application.ActiveSheet.Range("D3:F6");

let d3f4Range = range.Resize(2);
let d3e4Range = range.Resize(2, 2);
let d3h7Range = range.Resize(5, 5);
Range.ActivateFunction that activates a specific cell within the selected range when the range is selectedlet range = instance.Application.ActiveSheet.Range("A1:B2");
let a1Cell = instance.Application.ActiveSheet.Range("A1");

range.Select();
a1Cell.Activate();
Range.AutoFitFunction that appropriately changes the size when the specified range is an object managed by row or column unitslet a1Cell = instance.Application.ActiveSheet.Range("A1");

a1Cell.Columns.AutoFit(); // Auto-adjust column A width
a1Cell.Rows.AutoFit(); // Auto-adjust row 1 height
Range.CalculateFunction that recalculates formulas within the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

range.Calculate();
Range.ClearFunction that removes data, formatting, etc. within the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

range.Clear();
Range.ClearContentsFunction that removes data within the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

range.ClearContents();
Range.ClearFormatsFunction that removes formatting within the specified rangelet range = instance.Application.ActiveSheet.UsedRange;

range.ClearFormats();
Range.ColumnDifferencesFunction that returns a range object with cells that have different data from a specific cell within the range managing the specified columnlet range = instance.Application.ActiveSheet.Range("A1:A4");
let a1Cell = instance.Application.ActiveSheet.Range('A1');

range.Value2 = [ ['Example'], ['ohter'], ['Example'], ['other2'] ];
console.log(range.ColumnDifferences(a1Cell).Address()); // Outputs $A$2,$A$4 to console
Range.DataSeriesFunction that adds continuous data to the specified rangelet range = instance.Application.ActiveSheet.Range("A1:A12");
range.End(instance.Enums.XlDirection.xlUp).Formula = "31-JAN-1996"; // Input data in A1 cell

range.DataSeries({
Type: instance.Enums.XlDataSeriesType.xlChronological,
Date: instance.Enums.XlDataSeriesDate.xlMonth
}); // Input continuous data based on months in each cell A1:A12
Range.DeleteFunction that removes the specified rangelet range = instance.Application.ActiveSheet.Range("A1:B2");

range.Delete(); // After A1:B2 range is removed, rows 1~2 are pulled to the left
range.Delete(instance.Enums.XlDeleteShiftDirection.xlShiftUp); // After A1:B2 range is removed, columns A~B are pulled

range.Rows.Delete(); // Remove rows 1~2
range.Columns.Delete(); // Remove columns A~B
Range.FillDownFunction that fills the remaining range with the data from the topmost range of the specified rangelet range = instance.Application.ActiveSheet.Range("A1:C3");
range.Value2 = [ [1, 2, 3], [4, 5, 6], [7, 8, 9] ];
// A B C
// 1 2 3
// 4 5 6
// 7 8 9

range.FillDown();
// A B C
// 1 2 3
// 1 2 3
// 1 2 3
Range.FillLeftFunction that fills the remaining range with the data from the rightmost range of the specified rangelet range = instance.Application.ActiveSheet.Range("A1:C3");
range.Value2 = [ [1, 2, 3], [4, 5, 6], [7, 8, 9] ];
// A B C
// 1 2 3
// 4 5 6
// 7 8 9

range.FillLeft();
// A B C
// 3 3 3
// 6 6 6
// 9 9 9
Range.FillRightFunction that fills the remaining range with the data from the leftmost range of the specified rangelet range = instance.Application.ActiveSheet.Range("A1:C3");
range.Value2 = [ [1, 2, 3], [4, 5, 6], [7, 8, 9] ];
// A B C
// 1 2 3
// 4 5 6
// 7 8 9

range.FillRight();
// A B C
// 1 1 1
// 4 4 4
// 7 7 7
Range.FillUpFunction that fills the remaining range with the data from the bottommost range of the specified rangelet range = instance.Application.ActiveSheet.Range("A1:C3");
range.Value2 = [ [1, 2, 3], [4, 5, 6], [7, 8, 9] ];
// A B C
// 1 2 3
// 4 5 6
// 7 8 9

range.FillUp();
// A B C
// 7 8 9
// 7 8 9
// 7 8 9
Range.InsertFunction that inserts an empty range into the specified range
cf) Format of adjacent range is replicated		let range = instance.Application.ActiveSheet.Range("A1:B2");

range.Insert(); // After A1:B2 range is inserted, rows 1~2 are pushed to the right
range.Rows.Insert(); // Add rows 1~2
range.Columns.Insert(); // Add columns A~B
Range.RowDifferencesFunction that returns a range object with cells that have different data from a specific cell within the range managing the specified rowlet range = instance.Application.ActiveSheet.Range("A1:D1");
let a1Cell = instance.Application.ActiveSheet.Range('A1');

range.Value2 = ['Example', 'ohter', 'Example', 'other2'];
console.log(range.RowDifferences(a1Cell).Address()); // Outputs $B$1,$D$1 to console
Range.SelectFunction that processes the specified range as selectedlet range = instance.Application.ActiveSheet.UsedRange;

range.Select();

Spreadsheet Basic Functions , Engine Global Management , Integrated Document Management , Integrated Document , Sheet Management , Sheet , Area , Engine Object Common