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

Question

I am trying to select all the cells in an Excel sheet in the Excel.Range object of C# for applying auto fit, border etc. I have some merged cells within the sheets.

Is there any simple trick to do so?

Answer 1

Excel.Range theRange = (Excel.Range)CurrentSheet.UsedRange;

In this example, CurrentSheet is the variable where you have stored the sheet you are currently using.

Answer 2

public void refreshSheetColumsSize(Worksheet ws)
 {
    ws.get_Range("a1").EntireRow.EntireColumn.Select();         
 }

Answer 3

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

Answer 4

Officially, Excel.Worksheet.UsedRange.Rows and Excel.Worksheet.UsedRange.Columns.

In practice, it's buggy, you have to subtract the start row and column. The closest-to-correct answer is:

   Public ReadOnly Property LastColumn() As Integer
        Get
            Return ExcelWorksheet.UsedRange.Columns.Count + _
                   ExcelWorksheet.UsedRange.Column - 1
        End Get
    End Property
    Public ReadOnly Property LastRow() As Integer
        Get
            Return ExcelWorksheet.UsedRange.Rows.Count + _
                   ExcelWorksheet.UsedRange.Row - 1
        End Get
    End Property

This returns at least all the used cells, sometimes a little more. The 'little more' can be due to blank cells (rather than empty), and other random things. Form the research I did, this is the best that can be done.

If you really want to select everything then

ExcelWorksheet.Activate()
ExcelWorksheet.Cells.Select()

Answer 5

I've not done any excel development for a while (Excel 2003) but I always found that recording a macro while performing the tasks I was wanting to implement in code have sufficient pointers to help.

In this case, selecting all cells and autofitting gives the code:

Sub Macro1()
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

which I would imagine would roughly translate to:

((Microsoft.Office.Interop.Excel.Range)_sheet.Cells.Select()).AutoFit();

where _sheet is the instance of the Worksheet you're using. (untested)