Nate Oliver's blog

Good evening,

It's common to iterate through cells. Is it common to go through them, backwards? Perhaps. I cooked up a couple of trivial examples, in my opinion, for the this blog entry. Think about this, you're being interviewed about your Excel skills, and are asked to iterate through cells, the best response is "Which direction would you prefer?" Let's go backwards.

This VBA procedure reads in the opposite manner one might expect, backwards, through A1:C20:

Sub foo()
Dim strArr() As Variant
Dim i As Long, j As Long
Let strArr = Range("A1:C20").Value
For i = UBound(strArr, 1) To _
    LBound(strArr, 1) Step -1
    For j = UBound(strArr, 2) To _
        LBound(strArr, 2) Step -1
        Debug.Print strArr(i, j)
    Next j
Next i
End Sub

But it doesn't end there, of course. We can write to the grid with this logic, as well, eh. E.g.,

Sub bar()
Dim strArr() As Variant
Dim i As Long, j As Long
Let strArr = Range("A1:C20").Value
For i = UBound(strArr, 1) To _
    LBound(strArr, 1) Step -1
    For j = UBound(strArr, 2) To _
        LBound(strArr, 2) Step -1
        Let strArr(i, j) = "Hello World " & _
            UBound(strArr, 1) - i + 1 & " " & j
    Next j
Next i
Let Range("A1").Resize( _
    UBound(strArr, 1), UBound(strArr, 2)).Value = _
        strArr
End Sub

The 2nd Sub is a little convoluted in that I'm reading bottom-up, while writing top-down. They're simply meant to serve as working examples.

The point being that Excel is really flexible, and you can work with the grid in almost anyway you want. E.g., I picked a Range, it could have been any Range, I could have dropped off Columns, etc...

Happy Excelling,
Nate

(Written and tested in Excel 2010 - should be backwards compatible)