Loop through each cell in a range of cells when given a Range object

Sub LoopRange()

    Dim rCell As Range
    Dim rRng As Range

    Set rRng = Sheet1.Range("A1:A6")

    For Each rCell In rRng.Cells
        Debug.Print rCell.Address, rCell.Value
    Next rCell

End Sub

You could use Range.Rows, Range.Columns or Range.Cells. Each of these collections contain Range objects.

Here's how you could modify Dick's example so as to work with Rows:

Sub LoopRange()

    Dim rCell As Range
    Dim rRng As Range

    Set rRng = Sheet1.Range("A1:A6")

    For Each rCell In rRng.Rows
        Debug.Print rCell.Address, rCell.Value
    Next rCell

End Sub

And Columns:

Sub LoopRange()

    Dim rCell As Range
    Dim rRng As Range

    Set rRng = Sheet1.Range("A1:A6")

    For Each rCol In rRng.Columns
        For Each rCell In rCol.Rows
            Debug.Print rCell.Address, rCell.Value
        Next rCell
    Next rCol

End Sub

To make a note on Dick's answer, this is correct, but I would not recommend using a For Each loop. For Each creates a temporary reference to the COM Cell behind the scenes that you do not have access to (that you would need in order to dispose of it).

See the following for more discussion:

How do I properly clean up Excel interop objects?

To illustrate the issue, try the For Each example, close your application, and look at Task Manager. You should see that an instance of Excel is still running (because all objects were not disposed of properly).

A cleaner way to handle this is to query the spreadsheet using ADO:

http://technet.microsoft.com/en-us/library/ee692882.aspx