How to Get the Range of Occupied Cells in Excel Sheet

Find range of filled contents in Excel worksheet

If wb is defined as Excel Workbook, then this is a good way:

print (wb.sheets[sheet_name].api.UsedRange.Address)

How to Select all the cells in a worksheet in Excel.Range object of c#?

Taken from here, this will select all cells in the worksheet:

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select

How to find a range of cells? Excel C#

Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
Range rng = sheet.UsedRange;
foreach (Range column in from Range row in rng.Rows from Range column in row.Columns where column.Value == "T" select column)
{
   column.Interior.Color = Color.Red;
}

This piece of code will select the active worksheet, select the entire used range, iterate through its rows and columns and colorize all those cells that contain value "T".

If you cannot understand the first solution, you can use something like this that is a bit more easy on the eyes:

foreach (Range row in rng.Rows)
{
    foreach (Range column in row.Columns)
    {
        if (column.Value == "test")
        {
            column.Interior.Color = Color.Red;
        }
    }
}

---

Related Topics