Iterating through all the cells in Excel VBA or VSTO 2005

If you only need to look at the cells that are in use you can use:

sub IterateCells()   For Each Cell in ActiveSheet.UsedRange.Cells      'do some stuff   NextEnd Sub

that will hit everything in the range from A1 to the last cell with data (the bottom right-most cell)

Sub CheckValues1()    Dim rwIndex As Integer    Dim colIndex As Integer    For rwIndex = 1 To 10            For colIndex = 1 To 5                If Cells(rwIndex, colIndex).Value <> 0 Then _                    Cells(rwIndex, colIndex).Value = 0            Next colIndex    Next rwIndexEnd Sub

Found this snippet on http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Checksvaluesinarange10rowsby5columns.htm It seems to be quite useful as a function to illustrate the means to check values in cells in an ordered fashion.

Just imagine it as being a 2d Array of sorts and apply the same logic to loop through cells.

If you're just looking at values of cells you can store the values in an array of variant type. It seems that getting the value of an element in an array can be much faster than interacting with Excel, so you can see some difference in performance using an array of all cell values compared to repeatedly getting single cells.

Dim ValArray as VariantValArray = Range("A1:IV" & Rows.Count).Value

Then you can get a cell value just by checking ValArray( row , column )