Excel data export fixing number errors/removing green triangles

Question

After exporting data using a third party component the data in the excel sheet isn't type correctly. Excel thinks that some values are string while they are numbers and a little green triangle shows up.

We've coded the following to fix this:

For Each objCell As Microsoft.Office.Interop.Excel.Range In objWorkSheetReport.Range(objWorkSheetReport.Cells(1, 1), objWorkSheetReport.Cells(Me.RowCount + 10, Columns.Count + 10)).Cells
    If IsNumeric(objCell.Value) Then
        objCell.Value = CDbl(objCell.Value)
    End If
Next

This removes all those little green triangles but is really slow.

The question

Is there a faster way to convert a Range of data quickly so the green triangles don't show up?

Answer 1

Use the .SpecialCells() method of the range to narrow it down to only those cells that need to be changed.

Assuming a range NarrowedRange and a worksheet Sheet (substitute your own range for A1:A8, and objWorksheetReport for your sheet)

NarrowedRange = 
    Sheet.Range("A1:A8").SpecialCells(Excel.XlCellType.xlCellTypeConstants,
                                      Excel.XlSpecialCellsValue.xlTextValues)

will get you only the text value elements of your original range, so just change those accordingly.