CODEWITHSUNDEEP
c#.netexcelms-officeoffice-interop
Indigo
Indigo

Reputation: 2997

Fastest way to enumerate or look for all empty Excel cells and change their style or do some other processing

Which would be potentially a best way to enumerate or iterate or simply look for empty cells or cells with specific data structure in Excel, and later once you find it do some processing on it.

I tired Range, Value, Value2, etc but it takes fairly long time when Excel Sheet is considerably larger. I believe there must be some other efficient way. It would be nice, if you can show some example snippet.

Upvotes: 0

Views: 645

Answers (2)

David Zemens
David Zemens

Reputation: 53623

To find blank cells, use the .SpecialCells method of a range object.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.specialcells(v=office.11).aspx

The .specialCells method returns a range object of the matching criteria (i.e., xlCellTypeVisible, xlCellTypeBlanks, etc.). You can then iterate of this range to perform your formatting, etc.

Update I'm not a C# programmer, but I can show you how I would do this in VBA. Assuming interop exposes most/all of the same methods and functionality, you should hopefully be able to translate this for your purposes.

Sub ColorVisibles()
Dim rng As Range
Dim rngBlanks As Range
Dim blanksExist As Boolean

'define your range
Set rng = Range("A1:AA300")

'check to make sure there are blank cells in the range:
blanksExist = Application.WorksheetFunction.CountBlank(rng) > 0

If blanksExist Then
    Set rngBlanks = rng.SpecialCells(xlCellTypeBlanks)
    rngBlanks.Interior.Color = vbYellow

Else:
    MsgBox "No blank cells exist in the specified range.", vbInformation
End If

End Sub

Upvotes: 1

Christian Sauer
Christian Sauer

Reputation: 10889

The answer is relativley simple: get the array in one batch from excel (search SO for a how to) - test the values of the erray for empty cells and then acess only the empty cells in excel.

It is somewhat cumbersome, but the fastes way because iterating each cell is vastly slower than simply getting all data in a batch.

Upvotes: 1

Related Questions