Limiting a Range for Looping

The For Each construct can be used to loop through a range. By limiting the range, you can speed up your code. The Intersect function is a good way to limit the range. For instance, to convert everything in column A to proper case, you can use a sub like this:

Sub ConvertAToProper()

    Dim rCell As Range
    For Each rCell In Sheet1.Columns(1).Cells
        If Not IsEmpty(rCell.Value) Then
            rCell.Value = StrConv(rCell.Value, vbProperCase)
        End If
    Next rCell
End Sub

To speed up the code, limit the looping range like this:

For Each rCell In Intersect(Sheet1.Columns(1), Sheet1.UsedRange).Cells

Since cells outside of the UsedRange couldn’t possibly pass the IsEmpty test, limiting the range to the UsedRange makes the loop faster.

Posted in Uncategorized

3 thoughts on “Limiting a Range for Looping

  1. Note that SpecialCells is even more limiting, as it only evaluates UsedRange, so

    For Each rCell In Sheet1.Columns(1).SpecialCells( _
    xlCellTypeConstants, xlTextValues)

    will be even faster if there are blank or numeric cells. It also avoids an intermediate reference resolution.

  2. Hi,

    thanks for your suggestion, but I prefer this line of code

    For Each rCell in Sheet1.UsedRange.Columns(1).Cells

    You don’t have to estimate the intersection of the UsedRange and the whole column A.

    Anyway, have a nice weekend!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.