On Sep 8, 6:14*pm, Ron Rosenfeld wrote:
On Thu, 8 Sep 2011 16:40:09 +0100, Walter Briscoe wrote:
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.

I haven't measured that either, but I suspect whether it is faster or not may depend on the nature and amount of the data











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.

It is always difficult for me to respond comprehensively to a question where incomplete specifications are provided.

In this case, it turns out that the "do x" refers to "hide the row", suggesting a very different solution.

After discovering that additional requirement, I suggest the AutoFilter, possible with the dropdown box made invisible.

If he wants to move those visible rows to another sheet, I would then suggest the Advanced Filter, which has that capability.

Hi Ron, Walter
My apo being a newbie. However both of you given me food for thought.
1. For each next
2. Select case
3. do loop
4. Find FindNext
5. Auto filtering
I will experiment although i was thinking more in terms of minimising
hitting VBA and Excel, as i understand that's what slows or extends
the time taken.
Note also that when i use the find dialog (Ctrl+F) then Ctrl A to
select all, it seems much much faster on a bigger data. More like
instant!

Thks a million for extending my knowledge.
Rgds
Kz