In message of Thu, 8 Sep
2011 10:23:58 in microsoft.public.excel.programming, Ron Rosenfeld
writes
On Thu, 8 Sep 2011 02:55:46 -0700 (PDT), Kieranz
wrote:

Hi
I have a single column with lots of rows (over 1000s) containing
single digit number eg 0, 1, 2, 3 etc. What i need is an efficient
code to replace selection and then offset. eg

[snip]

Dim rg As Range, c As Range
Set rg = Range("c3", Cells(3, 3).End(xlDown))
For Each c In rg
If c.Value = 1 Then 'do x
Next c
End Sub

For this case, I would do something like

Dim c as Range, FirstAddress As String

set c = Range("c3", Cells(3, 3).End(xlDown)).find(What:=1)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
' do x
Set c = .FindNext(c)
Loop While c.Address < FirstAddress
End If

I prefer that loop because it only matches cells where something is to
be done. I infer you used For Each for symmetry with other examples.
OTOH, you may have information that your construct takes less time.
I would suspect not - fewer code lines are executed - but have not
measured.



In addition, if you are going to do different "x's" depending on the
contents of the cell, then consider:

Dim rg As Range, c As Range
Set rg = Range("c3", Cells(3, 3).End(xlDown))
For Each c In rg
Select Case c.Value
Case Is = 1

Why do you use "Case Is = 1" in preference to "Case 1"?
I infer that you use "Is comparisonoperator expression" rather than
"expression" because the former is more flexible and the latter
duplicates the functionality of the former.

'do x
Case Is = 2
'do y
Case Else
'do z
End Select
Next c

--
Walter Briscoe