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:
Dim rCell As Range
For Each rCell In Sheet1.Columns(1).Cells
If Not IsEmpty(rCell.Value) Then
rCell.Value = StrConv(rCell.Value, vbProperCase)
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.