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.
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.
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
Ben: I’m ashamed that I’ve never used that. It’s much better.