Which is faster?

[Regouin]

Hi guys I am speeding up a sub and already brought it down from 8 sec to 1.3 seconds. I shall first post the VBA.

Option Explicit

Sub bepaalonderhoud()
Dim start, finish, totaltime
start = Timer
'initialiseren listbox
With lbox1
.ColumnCount = 1
.ColumnWidths = 180
.Width = 200
.Height = 15
.Visible = False
End With
'leegmaken listbox en het lettertype van kolom A resetten naar niet-vet en 10 pts
lbox1.Clear
Worksheets("hoofd").Range("a7:a65536").Formula = ""
'eerste item listbox is 1
Me.lbox1.AddItem "1"
'rijnummers onderdelen opslaan in de listbox
Dim ws As Worksheet, cel As Range, rng As Range, firstAddy As String
Set ws = Worksheets("onderhoud")
Set rng = ws.Range("b1:b" & ws.Range("b65536").End(xlUp).Row)
With rng
Set cel = .Find("Onderdeel", LookIn:=xlValues, _
searchorder:=xlByRows, SearchDirection:=xlNext, LookAt:=xlPart, MatchCase:=False)
If Not cel Is Nothing Then
firstAddy = cel.Address
Do
Me.lbox1.AddItem cel.Row
Set cel = .FindNext(cel)
Loop Until cel Is Nothing Or cel.Address = firstAddy
End If
End With
'laatste item listbox is het rijnummer van "einde"
Set ws = Worksheets("onderhoud")
Set rng = ws.Range("a1:a" & ws.Range("a65536").End(xlUp).Row)
With rng
Set cel = .Find("Einde", LookIn:=xlValues, _
searchorder:=xlByRows, SearchDirection:=xlNext, LookAt:=xlPart, MatchCase:=False)
Me.lbox1.AddItem cel.Row
End With
'voor ieder onderdeel de taken bepalen en indien een taak moet gebeuren "ohoudtoev" aanroepen
Dim i As Long
Dim j As Long
Dim StartI As Long
Dim EindI As Long
Dim RangeHI As Range
j = 0
Do
StartI = lbox1.List(j, 0)
EindI = lbox1.List(j + 1, 0) - 1
For i = StartI To EindI
Set RangeHI = Worksheets("onderhoud").Range("H" & i)
If RangeHI.Value <> "" Then
Call ohoudtoev(i)
End If
Next i
j = j + 1
Loop Until j = lbox1.ListCount - 1
'alle rijen automatisch resizen
Worksheets("hoofd").Rows.AutoFit
Worksheets("hoofd").Range("A1").Select
finish = Timer
totaltime = finish - start
MsgBox (totaltime)
End Sub
 
Private Sub ohoudtoev(ByVal rij As Long)
Dim i As Long
Dim RangeAI As Range
Dim RangeHI As Range
Dim RangeBI As Range
Set RangeHI = Worksheets("onderhoud").Range("A" & rij)
Set RangeBI = Worksheets("onderhoud").Range("B" & rij)
'eerstvolgende lege cel zoeken op de eerste sheet
With Worksheets("hoofd")
i = 6
Do
i = i + 1
Loop Until .Range("A" & i) = ""
Set RangeAI = .Range("A" & i)
End With
'wegschrijven tekst
RangeHI.Copy
RangeAI.Select
ActiveSheet.Paste
End Sub

But later on it will import more data and it is going to take longer, and what loop is faster the

i = a
do
i = i+1
loop until i = b

or the

for i is a to b
next i

TIA
frank

[Killian]

My understanding of the situation is this:
A For .. Next loop is much faster. The "For i Is a To b" is only evaluated once and the bounds for the interation are set once. "Do... Until" or "While" performs an iteration then tests to see if the "While/Until" condition is met each time.

[Regouin]

So for set ranges (ie i = 1 to 532) it is better to use for next and for conditionary loops

i.e.

i = 6 
Do 
i = i + 1 
 Loop Until .Range("A" & i) = ""

a do loop would be better

[Killian]

Yes, if you know the range, use For... Next but in your case, you need to test the cell is empty so you need to use Do... Until
However, have you considered another approach? I assume you are trying to set the range of cells that contains data - I tend to use the CurrentRegion method for this e.g. [VBA]r = ActiveCell.CurrentRegion.Rows.Count[/VBA]

[Regouin]

with that method is it necessary to have a cell in the region active? Because when all the coding is done I want the sheet to entirely operate through userforms and generates a Word document or excel worksheet as report. Pretty much done with coding the rest now, just looking to tune up every here and there.

I made some minor adjustments to shave some time off

changed

Worksheets("hoofd").Range("a7:a65536").Formula = ""

into

Worksheets("hoofd").Range("a7:a65536").clear

and now the sub takes 0.75 seconds to complete

[Norie]

Do you really need to clear down to row 65536?

Why not clear down to the last row with data in column A?

Worksheets("hoofd").Range("A7:A" & Worksheets("hoofd").Range("A65536").End(xlUp).Row).ClearContents

[Regouin]

thank you, but it hardly makes a difference in the speed of the sub.

i changed the code
from

Worksheets("hoofd").Range("A7:A" & Worksheets("hoofd").Range("A65536").End(xlUp).Row).ClearContents

to

Worksheets("hoofd").Range("A7:A" & Worksheets("hoofd").Range("A65536").End(xlUp).Row).Clear

Since clear seems to be a lot faster (.2 second) then clearcontents

[Killian]

No, you don't need to activate the cell (which takes time)

r = Range("A6").CurrentRegion.Rows.Count

also works.
Selecting cells/ranges also takes extra time, so where you copy/paste the ranges at the end, you could use

RangeHI.Copy Destination:=RangeAI

With these methods, I think you can remove a few lines of code and maybe save a bit more time

[Regouin]

the current region always returns the same number doesnt matter how many cells are actually filled with data, Am I missing something here?

[edit]
found the problem had to select the proper sheet
[/edit]


[edit2]
But this method is actually slower then determining the next empty cell with a do... loop, it's only 0.04 seconds but I was expecting it to be faster since it doesnt have to check every time.
[/edit2]


[edit3]
now it takes just a little bit more then half a second which should be could, might become a little bit more when the sheets expand but should not grow too outrageous. I am going to mark this solved.
[/edit3]

[Zack Barresse]

The bottom line is, if you can get rid of your loops, your code will run faster. Granted, there are cases where this is not possible. So just cut down on them as much as possible, while utilizing Excel's native functionality.

Also on a side note, these two are very different ...
[vba]Do Until xxx
'...
Loop[/vba]
.. and ..
[vba]Do
'...
Loop Until xxx[/vba]
In the first example, both the Do Until and the Loop lines will be read on every iteration. In the second example, the Do will only be read once, thus effectively taking out one leg during runtime and shortening the length of time your code will run. Basically the second example is more efficient in this type of loop.

To see this for yourself, setup a couple examples, then step through your code with the F8 key. You will see what is evaluated and what is not.


HTH

[Colo]

Hi all, seems interesting topic. I think using an array is faster.
Generally, accessing to the cells property takes time.
So I tend to use getting values as an array then loop though the array.

In my code, For Each Next statement may faster than For Next Loop statement.
Also, absolute cell address is (a little bit) faster.
With statement is also a good approach.

Here is a sample code.

Private Sub ohoudtoev(ByVal rij As Long)
    Dim buf
    Dim i As Long
    With Sheets("hoofd")
        buf = .Range("$A$6", .Range("$A$65536").End(xlUp)).Value
        For i = LBound(buf) To UBound(buf)
            If buf(i, 1) = "" Then Exit For
        Next
        Worksheets("onderhoud").Range("$A$" & rij).Copy .Range("$A$" & i + 5)
    End With
End Sub

It's time to sleep for me. (3:15AM) Night All!

[brettdj]

Hi all, seems interesting topic. I think using an array is faster.

Absolutely

Using For loops with ranges is probably the most common inefficient technique (in terms of time) in VBA.

If possible its far better to use

1. Arrays (as per Colo's example above)
2. Find (delete row example, http://www.vbaexpress.com/kb/getarticle.php?kb_id=260)
3. SpecialCells
4. Filter / Formulas (delete groups or rows, http://www.vbaexpress.com/kb/getarticle.php?kb_id=371)

Cheers

Dave