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?

score 16 · Answer 1 · answered May 16 '12 at 08:22

16

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

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

answered May 16 '12 at 08:22

ThunderGr

2,297
29
20

1

Although the OP actually asked for "all cells", this is what I, and I suspect most other users were actually looking for. – primo Mar 06 '21 at 06:14

score 14 · Answer 2 · answered Nov 08 '11 at 16:16

14

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

answered Nov 08 '11 at 16:16

jocelyn

788
6
12

1

Looks nice, but i got this exception `Cannot implicitly convert type 'bool' to 'Microsoft.Office.Interop.Excel.Range'` – Yola Mar 16 '15 at 07:28
get_range should return a range and not a bool : https://msdn.microsoft.com/fr-fr/library/microsoft.office.tools.excel.worksheet.get_range.aspx call ws.get_Range("a1"); in a separate row and debug the result. – jocelyn Mar 17 '15 at 09:22
1

removing the `Select()`will do it. I was getting the same exception as Yola. I mean, create a method that returns a `Range`and return `get_Range("a1").EntireRow.EntireColumn` – Veverke Jun 27 '16 at 16:06

SwDevMan81 · Accepted Answer · 2011-06-20T15:38:36.093

12

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

edited Jun 20 '11 at 15:38

answered Jan 31 '11 at 12:32

SwDevMan81

48,814
22
151
184

score 3 · Answer 4 · answered Jan 31 '11 at 12:50

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()

NeverHopeless · Answer 5 · 2012-08-07T05:25:13.520

To consider all cells of a worksheet you can write like:

workSheet.Cells[workSheet.Rows.Count,workSheet.Columns.Count]

OR

 workSheet.get_Range("A1","IV65536")

To consider used cells of a worksheet, you can write:

workSheet.Rows.SpecialCells(XlCellType.xlCellTypeLastCell, XlSpecialCellsValue.xlTextValues)

Where "worksheet" represents the sheet you are working on. Both code sample returns a range.

Hope it helps!

score 2 · Answer 6 · answered Jan 31 '11 at 12:39

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)

score 1 · Answer 7 · answered May 11 '11 at 23:30

1

Much cleaner and doesn't depend on the number of rows/cols which were increased in Excel 2007:

Provided your sheet is in a variable called wsData:

wsData.Range(wsData.Cells(1, 1), wsData.Cells(wsData.Rows.Count, wsData.Columns.Count))

answered May 11 '11 at 23:30

Chris

11
1

1

For me wsData.Rows.Count always return 65536 instead of used rows FYI – jpsimard-nyx Jun 20 '11 at 13:24

score 0 · Answer 8 · answered Jul 06 '14 at 15:45

0

Very simple:

xlWorkSheet.UsedRange.Columns.Select()

answered Jul 06 '14 at 15:45

Lev Z

742
9
17

score 0 · Answer 9 · answered May 08 '15 at 16:14

0

worksheet.Columns.AutoFit()

Where "worksheet" is a variable of type Worksheet

answered May 08 '15 at 16:14

pmartin

2,731
1
25
30

score 0 · Answer 10 · answered Mar 26 '16 at 11:53

0

        xlWorksheet.get_Range("a1").EntireRow.EntireColumn.AutoFit();
        xlWorksheet.get_Range("a1").EntireColumn.EntireRow.AutoFit();

After cell filling has been finished.

answered Mar 26 '16 at 11:53

user6117410

1

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

10 Answers10

Linked