« Intersecting Ranges
Creating Folders »

Limiting a Range for Looping

7th October 2004, 12:49 pm by Dick Kusleika

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.

Category: VBA Basics, Worksheet Object  |  Comment (RSS)

3 Comments

  1. J.E. McGimpsey:

    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.

    7 October 2004, 3:41 pm

  2. Ben:

    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!

    Greetz

    19 October 2004, 6:23 am

  3. Dick:

    Ben: I’m ashamed that I’ve never used that. It’s much better.

    19 October 2004, 10:51 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment