Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Rows and Columns / Rows or Columns That Have Data
In This Topic
    Rows or Columns That Have Data
    In This Topic

    You can work with the rows and columns of the sheet and distinguish which ones have data by using various members of the SheetView class. You can use the following methods available on the sheet:

    You can return the number of rows and columns that have data using these properties:

    Row/Column with Source Range 

    It is also possible to return the last row and column within a specified range of a sheet using the IRange.End method. This method accepts two parameters i.e. direction enum to specify the direction and includeHidden parameter to specify whether to include hidden rows and columns.

    Refer to the following code to return the cell at the end of specified source range in left direction.

    C#
    Copy Code 
    // IRange.End(Direction.Left)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["A1"].Value = 1;
TestActiveSheet.Cells["C1:F1"].Value = 1;
TestActiveSheet.Columns[1].Hidden = true;
TestActiveSheet.Columns[5].Hidden = true;
// No data left source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Left, false).Address());  //or true. return A5
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Left, false).Address());  //return A1
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Left, true).Address());  //return C1
// There is data left source range
Debug.WriteLine(TestActiveSheet.Cells["I1"].End(Direction.Left, false).Address());  //return E1
Debug.WriteLine(TestActiveSheet.Cells["I1"].End(Direction.Left, true).Address());  //return F1
    Visual Basic
    Copy Code 
    ' IRange.End(Direction.Left)
Dim TestActiveSheet As IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
TestActiveSheet.Cells("A1").Value = 1
TestActiveSheet.Cells("C1:F1").Value = 1
TestActiveSheet.Columns(1).Hidden = True
TestActiveSheet.Columns(5).Hidden = True

' No data left source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Left, False).Address()) 'or true. return A5

' Source range inside data range

Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Left, False).Address()) 'return A1
Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Left, True).Address()) 'return C1

' There is data left source range
Debug.WriteLine(TestActiveSheet.Cells("I1").End(Direction.Left, False).Address()) 'return E1

Debug.WriteLine(TestActiveSheet.Cells("I1").End(Direction.Left, True).Address()) 'return F1

    Refer to the following code to return the cell at the end of specified source range in right direction.

    C#
    Copy Code 
    // IRange.End(Direction.Right)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["C1:F1"].Value = 1;
TestActiveSheet.Cells["H1"].Value = 1;
TestActiveSheet.Columns[2].Hidden = true;
TestActiveSheet.Columns[6].Hidden = true;
// No data right source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Right, false).Address());  //or true. return SF5
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Right, false).Address());  //return H1
Debug.WriteLine(TestActiveSheet.Cells["D1"].End(Direction.Right, true).Address());  //return F1
//There is data right source range
Debug.WriteLine(TestActiveSheet.Cells["A1"].End(Direction.Right, false).Address());  //return D1
Debug.WriteLine(TestActiveSheet.Cells["A1"].End(Direction.Right, true).Address());  //return C1
    Visual Basic
    Copy Code 
    ' IRange.End(Direction.Right)
Dim TestActiveSheet As IWorksheet = TestWorkBook.ActiveSheet
TestActiveSheet.Cells("C1:F1").Value = 1
TestActiveSheet.Cells("H1").Value = 1
TestActiveSheet.Columns(2).Hidden = True
TestActiveSheet.Columns(6).Hidden = True
' No data right source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Right, False).Address()) 'or true. return SF5
' Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Right, False).Address()) 'return H1
Debug.WriteLine(TestActiveSheet.Cells("D1").End(Direction.Right, True).Address()) 'return F1
' There is data right source range
Debug.WriteLine(TestActiveSheet.Cells("A1").End(Direction.Right, False).Address()) 'return D1
Debug.WriteLine(TestActiveSheet.Cells("A1").End(Direction.Right, True).Address()) 'return C1

    Refer to the following code to return the cell at the end of specified source range in upward direction.

    C#
    Copy Code 
    // IRange.End(Direction.Up)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["C1"].Value = 1;
TestActiveSheet.Cells["C3:C6"].Value = 1;
TestActiveSheet.Rows[1].Hidden = true;
TestActiveSheet.Rows[5].Hidden = true;
// No data above source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Up, false).Address()); //or true. return B1
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Up, false).Address());  //return C1
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Up, true).Address());  //return C3
// There is data above source range
Debug.WriteLine(TestActiveSheet.Cells["C10"].End(Direction.Up, false).Address());  //return C5
Debug.WriteLine(TestActiveSheet.Cells["C10"].End(Direction.Up, true).Address());  //return C6
    Visual Basic
    Copy Code 
    ' IRange.End(Direction.Up)
Dim TestActiveSheet As IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
TestActiveSheet.Cells("C1").Value = 1
TestActiveSheet.Cells("C3:C6").Value = 1
TestActiveSheet.Rows(1).Hidden = True
TestActiveSheet.Rows(5).Hidden = True
' No data above source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Up, False).Address()) 'or true. return B1
' Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Up, False).Address()) 'return C1
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Up, True).Address()) 'return C3
' There is data above source range
Debug.WriteLine(TestActiveSheet.Cells("C10").End(Direction.Up, False).Address()) 'return C5
Debug.WriteLine(TestActiveSheet.Cells("C10").End(Direction.Up, True).Address()) 'return C6

    Refer to the following code to return the cell at the end of specified source range in downward direction.

    C#
    Copy Code 
    // IRange.End(Direction.Down)
IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
TestActiveSheet.Cells["C3:C6"].Value = 1;
TestActiveSheet.Cells["C8"].Value = 1;
TestActiveSheet.Rows[2].Hidden = true;
TestActiveSheet.Rows[6].Hidden = true;
// No data below source range
Debug.WriteLine(TestActiveSheet.Cells["B5"].End(Direction.Down, false).Address());  //or true. return B500
// Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Down, false).Address());  //return C8
Debug.WriteLine(TestActiveSheet.Cells["C4"].End(Direction.Down, true).Address());  //return C6
// There is data below source range
Debug.WriteLine(TestActiveSheet.Cells["C1"].End(Direction.Down, false).Address());  //return C4
Debug.WriteLine(TestActiveSheet.Cells["C1"].End(Direction.Down, true).Address());  //return C3
    Visual Basic
    Copy Code 
    ' IRange.End(Direction.Down)
Dim TestActiveSheet As IWorksheet = TestWorkBook.ActiveSheet
TestActiveSheet.Cells("C3:C6").Value = 1
TestActiveSheet.Cells("C8").Value = 1
TestActiveSheet.Rows(2).Hidden = True
TestActiveSheet.Rows(6).Hidden = True
' No data below source range
Debug.WriteLine(TestActiveSheet.Cells("B5").End(Direction.Down, False).Address()) 'or true. return B500
' Source range inside data range
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Down, False).Address()) 'return C8
Debug.WriteLine(TestActiveSheet.Cells("C4").End(Direction.Down, True).Address()) 'return C6
' There is data below source range
Debug.WriteLine(TestActiveSheet.Cells("C1").End(Direction.Down, False).Address()) 'return C4
Debug.WriteLine(TestActiveSheet.Cells("C1").End(Direction.Down, True).Address()) 'return C3
    The IRange.End method returns the first cell inside the source range.

    See Also
    Rows and Columns
    Customizing the Number of Rows or Columns
    Adding a Row or Column
    Removing a Row or Column
    Showing or Hiding a Row or Column
    Setting the Row Height or Column Width
    Setting Fixed (Frozen) Rows or Columns
    Moving Rows or Columns
    Creating Alternating Rows
    Setting up Preview Rows
    Input Data in Rows or Columns
    Adding a Tag to a Row or Column