How to: Freeze and Unfreeze Rows and Columns

• Sep 13, 2019
• 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.

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.

RowAndColumnActions.cs

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);
    }
}

RowAndColumnActions.vb

Private Shared Sub FreezePanes(ByVal control As SpreadsheetControl)
    'Access the active worksheet.
    Dim worksheet As Worksheet = control.Document.Worksheets.ActiveWorksheet

    ' Access the cell range that is currently visible.
    Dim visibleRange As CellRange = control.VisibleRange

    ' Access the active cell. 
    Dim activeCell As Cell = control.ActiveCell

    Dim rowOffset As Integer = activeCell.RowIndex - visibleRange.TopRowIndex - 1
    Dim columnOffset As Integer = activeCell.ColumnIndex - visibleRange.LeftColumnIndex - 1

    ' If the active cell is outside the visible range of cells, no rows and columns are frozen.
    If (Not visibleRange.IsIntersecting(activeCell)) Then
        Return
    End If

    If activeCell.ColumnIndex = visibleRange.LeftColumnIndex Then
        ' If the active cell matches the top left visible cell, no rows and columns are frozen.
        If activeCell.RowIndex = visibleRange.TopRowIndex Then
            Return
        Else
            ' Freeze visible rows above the active cell if it is located in the leftmost visible column.
            worksheet.FreezeRows(rowOffset, visibleRange)
        End If

    ElseIf activeCell.RowIndex = visibleRange.TopRowIndex Then
        ' 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)
    End If
End Sub