Iterating through all the cells in Excel VBA or VSTO 2005

Question

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.

Solution 1

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 rwIndex
End 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.

Solution 2

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
   Next

End Sub

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

Solution 3

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 )

Solution 4

You can use a For Each to iterate through all the cells in a defined range.

Public Sub IterateThroughRange()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range

Set wb = Application.Workbooks(1)
Set ws = wb.Sheets(1)
Set rng = ws.Range("A1", "C3")

For Each cell In rng.Cells
    cell.Value = cell.Address
Next cell

End Sub

Solution 5

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.

View more solutions