How to: Freeze and Unfreeze Rows and Columns

• Nov 13, 2018
• 3 minutes to read

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

The following cases are handled:

• If an active cell is outside the visible range of cells 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.

To get the range of cells that is currently visible, use the SpreadsheetControl.VisibleRange property. To access a cell that is currently active in a worksheet, use the SpreadsheetControl.ActiveCell property.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-freeze-and-unfreeze-rows-and-columns-e4844

Form1.cs

private void buttonFreezePanes_Click(object sender, EventArgs e) {
    //Access the active worksheet.
    Worksheet worksheet = workbook.Worksheets.ActiveWorksheet;

    // Access the cell range that is currently visible.
    Range visibleRange = spreadsheetControl1.VisibleRange;

    // Access the active cell. 
    Cell activeCell = spreadsheetControl1.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);
    }
}

Form1.vb

Private Sub buttonFreezePanes_Click(ByVal sender As Object, ByVal e As EventArgs) Handles buttonFreezePanes.Click
    'Access the active worksheet.
    Dim worksheet As Worksheet = workbook.Worksheets.ActiveWorksheet

    ' Access the cell range that is currently visible.
    Dim visibleRange As Range = spreadsheetControl1.VisibleRange

    ' Access the active cell. 
    Dim activeCell As Cell = spreadsheetControl1.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

The image below shows how rows and columns are frozen relative to the active cell.

To custom paint frozen pane borders in the SpreadsheetControl, handle the SpreadsheetControl.CustomDrawFrozenPaneBorder event.