Worksheet.GetUsedRange() Method

Returns the used range on the specified worksheet.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

C#

Range GetUsedRange()

VB.NET

Function GetUsedRange As Range

Returns

Type	Description
Range	A Range object that is the range of modified cells.

Remarks

In the Spreadsheet document model, empty cells in a worksheet do not exist until they are accessed in some way or modified. The GetUsedRange method returns a worksheet range that complies with the requirements listed below.

1. The used range is a continuous range of non-empty cells. The top-left cell of the used range is a cell located at the intersection of the topmost row and the leftmost column containing non-empty cells. The bottom-right cell of the used range is a cell located at the intersection of the lowest row and the rightmost column containing non-empty cells.

   Note

   The cell is not empty if it contains a Range.Value, Range.Formula, or has any Formatting properties which are not null.

   

2. The used range is extended to include the leftmost and rightmost empty columns containing visual formatting (fill or border). However, the returned range cannot include more than 8192 columns. Hidden columns that contain no data or formatting options applied, as well as empty columns with a custom width are not included in the used range.

   

3. The used range is extended to include the topmost and bottommost empty rows containing visual formatting (fill or border).

   

4. The used range includes all the tables regardless their visibility state (a table occupies a range determined by the Table.Range property).

   

5. Pictures are not included in the used range.

   

6. Merged cells are included in the used range, even if they are located in hidden columns or rows.

   

7. Conditional formatting is ignored.

The following example shows how to use the GetUsedRange method to restrict the number of visible rows and columns in a worksheet:

C#

// Access the active worksheet.
Worksheet worksheet = spreadsheetControl.ActiveWorksheet;
// Get the range containing non-empty cells.
Range usedRange = worksheet.GetUsedRange();
// Restrict the worksheet's visible area to the used range.
spreadsheetControl.WorksheetDisplayArea.SetSize(worksheet.Index, usedRange.ColumnCount, usedRange.RowCount);

VB.NET

' Access the active worksheet.
Dim worksheet As Worksheet = spreadsheetControl.ActiveWorksheet
' Get the range containing non-empty cells.
Dim usedRange As Range = worksheet.GetUsedRange()
' Restrict the worksheet's visible area to the used range.
spreadsheetControl.WorksheetDisplayArea.SetSize(worksheet.Index, usedRange.ColumnCount, usedRange.RowCount)

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the GetUsedRange() method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-import-data-to-worksheet-cells-from-different-data-sources-and-use-tables-e4751/CS/DataImportExample/Form1.cs#L30

Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
worksheet.Clear(worksheet.GetUsedRange());
worksheet.Cells["A1"].ColumnWidthInCharacters = 35;

how-to-use-a-spreadsheet-control-as-an-in-place-editor-gridcontrol-propertygridcontrol-e4777/CS/WindowsFormsApplication1/Form1.cs#L54

Worksheet workSheet = spreadSheet.Document.Worksheets[0];
Range usedRange = workSheet.GetUsedRange();

wpf-spreadsheet-pivot-table-api-t314425/CS/SpreadsheetWPFPivotTableExamples/MainWindow.xaml.cs#L51

Worksheet firstSheet = workbook.Worksheets[0];
Range usedRange = firstSheet.GetUsedRange();
firstSheet.SelectedCell = usedRange[usedRange.RowCount * usedRange.ColumnCount - 1].Offset(1, 1);

winforms-spreadsheet-pivot-table-api-t298650/CS/SpreadsheetPivotTableExamples/Form1.cs#L101

Worksheet active = workbook.Worksheets.ActiveWorksheet;
Range usedRange = active.GetUsedRange();
active.SelectedCell = usedRange[usedRange.RowCount * usedRange.ColumnCount - 1].Offset(1, 1);

gridview-how-to-upload-an-excel-file-via-uploadcontrol-and-show-its-data-in-a-grid-t576892/CS/UploadControlApplication/Models/HelperClass.cs#L18

Worksheet sheet = book.Worksheets.ActiveWorksheet;
Range range = sheet.GetUsedRange();
DataTable table = sheet.CreateDataTable(range, false);

VB.NET

how-to-import-data-to-worksheet-cells-from-different-data-sources-and-use-tables-e4751/VB/DataImportExample/Form1.vb#L31

Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
worksheet.Clear(worksheet.GetUsedRange())
worksheet.Cells("A1").ColumnWidthInCharacters = 35

how-to-use-a-spreadsheet-control-as-an-in-place-editor-gridcontrol-propertygridcontrol-e4777/VB/WindowsFormsApplication1/Form1.vb#L56

Dim workSheet As Worksheet = spreadSheet.Document.Worksheets(0)
Dim usedRange As Range = workSheet.GetUsedRange()

wpf-spreadsheet-pivot-table-api-t314425/VB/SpreadsheetWPFPivotTableExamples/MainWindow.xaml.vb#L50

Dim firstSheet As Worksheet = workbook.Worksheets(0)
Dim usedRange As Range = firstSheet.GetUsedRange()
firstSheet.SelectedCell = usedRange(usedRange.RowCount * usedRange.ColumnCount - 1).Offset(1, 1)

winforms-spreadsheet-pivot-table-api-t298650/VB/SpreadsheetPivotTableExamples/Form1.vb#L97

Dim active As Worksheet = workbook.Worksheets.ActiveWorksheet
Dim usedRange As Range = active.GetUsedRange()
active.SelectedCell = usedRange(usedRange.RowCount * usedRange.ColumnCount - 1).Offset(1, 1)

gridview-how-to-upload-an-excel-file-via-uploadcontrol-and-show-its-data-in-a-grid-t576892/VB/UploadControlApplication/Models/HelperClass.vb#L18

Dim sheet As Worksheet = book.Worksheets.ActiveWorksheet
Dim range As Range = sheet.GetUsedRange()
Dim table As DataTable = sheet.CreateDataTable(range, False)