How to iterate through all the cells in Excel VBA or VSTO 2005


I need to simply go through all the cells in a Excel Spreadsheet and check the values in the cells. The cells may contain text, numbers or be blank. I am not very familiar / comfortable working with the concept of 'Range'. Therefore, any sample codes would be greatly appreciated. (I did try to google it, but the code snippets I found didn't quite do what I needed)

Thank you.


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 Variant
ValArray = Range("A1:IV" & Rows.Count).Value

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

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

End Sub

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

By : cori

For a VB or C# app, one way to do this is by using Office Interop. This depends on which version of Excel you're working with.

For Excel 2003, this MSDN article is a good place to start. Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective

You'll basically need to do the following:

  • Start the Excel application.
  • Open the Excel workbook.
  • Retrieve the worksheet from the workbook by name or index.
  • Iterate through all the Cells in the worksheet which were retrieved as a range.
  • Sample (untested) code excerpt below for the last step.

    Excel.Range allCellsRng;
    string lowerRightCell = "IV65536";
    allCellsRng = ws.get_Range("A1", lowerRightCell).Cells;
    foreach (Range cell in allCellsRng)
        if (null == cell.Value2 || isBlank(cell.Value2))
          // Do something.
        else if (isText(cell.Value2))
          // Do something.
        else if (isNumeric(cell.Value2))
          // Do something.

For Excel 2007, try this MSDN reference.

This video can help you solving your question :)
By: admin