Efficient looping

[Kieranz]

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

 

[Ron Rosenfeld]

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

 

[Kieranz]

Hi Ron
What i need to do is having found "1" that row must be hidden then
move down next row, check and if "1" then hide that row and so on. but
this iteration takes a long time. I had read somewhere it could be
done much faster similar to eg like Find dialog wherein you can find
all in one go do a Control + A to select all found and then do the
necessary like hide all found rows in one go.
Rgds
Kz

 

[Walter Briscoe]

In message <[email protected]> of Thu, 8 Sep
2011 10:23:58 in microsoft.public.excel.programming, Ron Rosenfeld

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.

 

[Ron Rosenfeld]

Hi Ron
What i need to do is having found "1" that row must be hidden then
move down next row, check and if "1" then hide that row and so on. but
this iteration takes a long time. I had read somewhere it could be
done much faster similar to eg like Find dialog wherein you can find
all in one go do a Control + A to select all found and then do the
necessary like hide all found rows in one go.
Rgds
Kz

It is always much more helpful to supply all of your requirements in your first post. You are more likely to obtain pertinent responses that way. As would have been the case here.


Since what you want to do is hide all the rows that contain a 1, using auto-filter would be much quicker.


Dim rg As Range
Set rg = Range("c3", Cells(3, 3).End(xlDown))
Set rg = rg.Offset(rowoffset:=-1).Resize(rowsize:=rg.Rows.Count + 1)
rg.AutoFilter Field:=1, Criteria1:="<>1"

Note that in order to use this, the first row of the range will never be hidden; so we have to expand it by one. If there is a label in C2, you could set up the range that way initially.

Also note there is an optional argument for the AutoFilter to hide the dropdown box.

 

[Ron Rosenfeld]

In message <[email protected]> of Thu, 8 Sep
2011 10:23:58 in microsoft.public.excel.programming, Ron Rosenfeld

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

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.

 

[Kieranz]

In message <[email protected]> of Thu, 8 Sep
2011 10:23:58 in microsoft.public.excel.programming, Ron Rosenfeld

On Thu, 8 Sep 2011 02:55:46 -0700 (PDT), Kieranz
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 notmay depend on the nature and amount of the data

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

 

[Ron Rosenfeld]

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

Glad to help. But for the specific problem of hiding rows containing "1" in Column C, the AutoFilter (or, if you want to move the visible rows elsewhere, AdvancedFilter) will be the fastest.

 

[Rick Rothstein]

I prefer that loop because it only matches cells where something is to

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

Assuming your data is constants, not formulas (although I can modify this
for that case), this will probably be one of the fastest methods to hide the
rows for cells with a number 1 in them - and notice, no loops whatsoever...

Sub HideRowsWithOnes()
With Columns("A")
.Replace 1, "=1", xlWhole
.SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True
.Replace "=", "", xlPart
End With
End Sub

Rick Rothstein (MVP - Excel)

 

[Kieranz]

Assuming your data is constants, not formulas (although I can modify this
for that case), this will probably be one of the fastest methods to hide the
rows for cells with a number 1 in them - and notice, no loops whatsoever....

Sub HideRowsWithOnes()
  With Columns("A")
    .Replace 1, "=1", xlWhole
    .SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True
    .Replace "=", "", xlPart
  End With
End Sub

Rick Rothstein (MVP - Excel)

Hi Ron, tried the autofilter and works like a charm. Thks. Learnt
something new!
Rick - i will try this out, very unusual to say the least, how would u
do it if the cell contained a formula. Will give it a shot over the
weekend and chk back on Monday no internet access!!!
Thks to both of u.
Hav a lovely weekend
Kz

 

[Rick Rothstein]

Rick - i will try this out, very unusual to say the least

Yes, I know the approach is "out of the box", but it is quite fast when
executing (Excel appears to be optimized "underneath it all" for
replacements and the SpecialCells function). While the code I posted
originally should work fine for you, I did leave out one statement that
should be in it to prevent an error from occurring just in case you chose to
run the code when there are no 1's in the data.

Sub HideRowsWithOnes()
With Columns("A")
.Replace 1, "=1", xlWhole
On Error Resume Next
.SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True
.Replace "=", "", xlPart
End With
End Sub

how would u do it if the cell contained a formula.

Same underlying method (just as fast though), but just a touch more work if
the data was produced by formulas as opposed being constant values.

Sub HideRowsWithOnes()
Dim StartRow As Long, LastRow As Long, UnusedColumn As Long
StartRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
Cells(StartRow, UnusedColumn).Resize(LastRow).Value = _
Cells(StartRow, "A").Resize(LastRow).Value
On Error Resume Next
With Columns(UnusedColumn)
.Replace "1", "", xlWhole
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.EntireColumn.Clear
End With
Application.ScreenUpdating = True
End Sub

Note that with this method, it is necessary to specify the start row for the
data (headers, if any, are constants and must be stepped over). There is a
StartRow variable at the beginning of the code where you can specify this
value (I set it to 2 in my code assuming there was a header row... change if
necessary).

Rick Rothstein (MVP - Excel)