How to: Freeze and Unfreeze Rows and Columns

  • 3 minutes to read

This example demonstrates how to freeze visible rows and columns above and to the left of an active cell in a worksheet.

Spreadsheet_Freeze_Panes_Example

The following cases are handled:

  • If an active cell is outside the visible cell range or it matches the top left visible cell, no rows and columns are frozen.

  • If an active cell is located in the leftmost visible column, only visible rows above the active cell are frozen. The Worksheet.FreezeRows method is called.

  • If an active cell is located in the topmost visible row, only visible columns to the left of the active cell are frozen. The Worksheet.FreezeColumns method is called.

  • Otherwise, both visible rows and columns above and to the left of the active cell are frozen. The Worksheet.FreezePanes method is called.

Use the SpreadsheetControl.VisibleRange property to obtain the currently visible range. To access an active cell, use the SpreadsheetControl.ActiveCell property.

static void FreezePanes(SpreadsheetControl control)
{
    //Access the active worksheet.
    Worksheet worksheet = control.Document.Worksheets.ActiveWorksheet;

    // Access the cell range that is currently visible.
    CellRange visibleRange = control.VisibleRange;

    // Access the active cell. 
    Cell activeCell = control.ActiveCell;

    int rowOffset = activeCell.RowIndex - visibleRange.TopRowIndex - 1;
    int columnOffset = activeCell.ColumnIndex - visibleRange.LeftColumnIndex - 1;

    // If the active cell is outside the visible range of cells, no rows and columns are frozen.
    if (!visibleRange.IsIntersecting(activeCell))
    {
        return;
    }

    if (activeCell.ColumnIndex == visibleRange.LeftColumnIndex)
    {
        // If the active cell matches the top left visible cell, no rows and columns are frozen.
        if (activeCell.RowIndex == visibleRange.TopRowIndex) { return; }
        else
            // Freeze visible rows above the active cell if it is located in the leftmost visible column.
            worksheet.FreezeRows(rowOffset, visibleRange);
    }

    else if (activeCell.RowIndex == visibleRange.TopRowIndex)
    {
        // Freeze visible columns to the left of the active cell if it is located in the topmost visible row.
        worksheet.FreezeColumns(columnOffset, visibleRange);
    }

    else
    {
        // Freeze both rows and columns above and to the left of the active cell.
        worksheet.FreezePanes(rowOffset, columnOffset, visibleRange);
    }
}