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

Range("C3").select
do while selection <""
if activecell.value = 1 then
'do x
endif
activecell.offset(1,0).select
loop

The above code can take a long time to print.
Any help appreciated. Thks
Kz

There is rarely any requirement to Select a cell, so you could do something like this:

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

If you are only going to do something if the cell value = 1, then

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


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
'do x
Case Is = 2
'do y
Case Else
'do z
End Select
Next c