Curious about why one macro runs so much quicker than another

Hi Guys,

From another thread (http://www.excelforum.com/excel-prog...30#post3213330)

My VBA is self taught and doubtless lacks good structure. One thing that I'm particularly curious about is the methods used between a macro that I wrote to solve the problem on the above thread and another written by another member whose code does essentially the same exercise but in a fraction of the time that mine runs.

My code was.

Sub s4driver()
Dim rng As Range
Dim LR As Long
Dim c As Range
LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
Set rng = Sheet3.Range("b2:b" & LR)
For Each c In rng
    If IsNumeric(Sheet3.Range(c.Address).Value) = False Or Left(Sheet3.Range(c.Address).Value, 2) = "00" Or Sheet3.Range(c.Address).Value = "" Then
        Sheet3.Range("a" & c.Row & ":h" & c.Row).ClearContents
    End If
Next c


End Sub

The better solution and enormously faster solution was posted by stanleydgromjr as follows.

Option Explicit
Sub ReorgAthruH()
' stanleydgromjr, 04/25/2013
' http://www.excelforum.com/excel-programming-vba-macros/918047-need-macro-to-remove-unwanted-data-from-large-tables.html
Dim a As Variant, b As Variant, lr As Long, i As Long, ii As Long, iii As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
a = Range("A2:H" & lr)
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
  If Left(a(i, 2), 2) = "00" Or a(i, 2) = "" Or a(i, 2) Like "*[A-Z,a-z]*" Then
    'do nothing
  Else
    iii = iii + 1
    For ii = 1 To UBound(a, 2)
      b(iii, ii) = a(i, ii)
    Next ii
  End If
Next i
Range("A2:H" & lr) = b
End Sub

I'm keen to get some help and advice on better coding practice and structure and also to understand why there is such a large difference in the execution time between the macros.

Many thanks for any help and advice.
Chris

Simple answer: Stan reads all the data into one array, processes it in memory creating a second array, and then outputs the second array back to the worksheet.

You are processing individual cells on the worksheet, one by one. This will probably means that a) the screen is updated and b) a calculation is performed every time you change a cell.

Try switch off ScreenUpdating before you start your loop and on again when you finish. And set Calculation to Manual before the loop and Automatic afterwards.

Regards, TMS

TMShucks,

Thanks for the explanation.

No problem. Hope I got it right and wasn't jumping in where I wasn't needed ... not sure how often you get on here and if you would have seen this.

Regards, TMS

@s4driver: seems I missed the moment as the thread has some replies and is marked Solved.

Regards, TMS

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

bodhi808,

I have read through Stan's code a number of times but I've yet to fully get my head around it.

I will add some text lines to the macro to explain how it functions.

Be back in a little while.

bodhi808,

I have read through Stan's code a number of times but I've yet to fully get my head around it.

I hope this helps.

See the text lines beginning with '' in the code:

'' the next line of code says that I will be defining my variables
''   this is a very good programming practice, because, it will help
''   other programmers to understand the workings of the macro.
Option Explicit


Sub ReorgAthruH()
' stanleydgromjr, 04/25/2013
' http://www.excelforum.com/excel-programming-vba-macros/918047-need-macro-to-remove-unwanted-data-from-large-tables.html
Dim a As Variant, b As Variant, lr As Long, i As Long, ii As Long, iii As Long


''  find the last used row in the worksheet
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row


'' create variant array a(1 to 45, 1 to 8)
a = Range("A2:H" & lr)


'' create variant array b the same size as array a
'' b(1 t 45, 1 to 8)
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))


'' using the i Long variable as a counter
''   loop from the first row of the array to the last row of the array
For i = 1 To UBound(a, 1)


  ''  *********************************************************** see below
  ''  we are checking each item in the second column of the array
  ''    if Left(a(i,2),2) = "00"
  ''    Or a(i,2) = ""
  ''    Or a(i,2) Like "*[A-Z,a-z]*" contains any alpha upper/lower case characters
  ''    then do not write the items in the row to the b array
  If Left(a(i, 2), 2) = "00" Or a(i, 2) = "" Or a(i, 2) Like "*[A-Z,a-z]*" Then
    'do nothing


  ''  then write the items in the row to the b array
  Else


    '' increment the iii Long variable by 1
    iii = iii + 1
    
    
    '' loop from the 1st 'column like' item of the a array to the last 'column like' item in the row
    For ii = 1 To UBound(a, 2)
    
    
      '' write the items in the a array to the b array
      b(iii, ii) = a(i, ii)
      
      
    '' and loop
    Next ii
  End If


''  And loop to the next row in the a array
Next i


'' write the b array which is the same size as the a array beginning in A2
''   but, the b array does not contain any of the rows of information that
''   we did not want to write
  ''  *********************************************************** see above
  ''  we are checking each item in the second column of the array
  ''    if Left(a(i,2),2) = "00"
  ''    Or a(i,2) = ""
  ''    Or a(i,2) Like "*[A-Z,a-z]*" contains any alpha upper/lower case characters
  ''    then do not write the items in the row to the b array
Range("A2:H" & lr) = b
End Sub

Stan, thank you so much for doing that, it's fantastic!

It's such an elegant (if VBA can be described that way) solution to the problem and a dynamic approach that I've never understood before. I expect to many seasoned VBA guys it's second nature, but I feel like a light bulb has come on haha.

I hadn't realised that the array can be populated by simply stating a = Range("A2:H" & lr) and conversely written back by to the cells in the same manner. I also thought that the array sizes had to be declared initially rather than declaring the arrays as variant. This really helps me to understand their use in simplifying some of my convoluted coding.

ReDim is something that I haven't come across before so I will read up on that too.
One thing from my basic understanding of arrays is that by default they start from position 0 unless specified, like in your ReDim line for array 'b'.
Does the 'a' array run from 0-7 and not 1-8? I had thought that the Ubound function also counted from position 0?
I assume they mustn't do because the loops cycle through the arrays without missing the last column when they both use 'ii' variable as the position across the array.

Again, thank you very much for taking the time to help me understand this, I really appreciate it and have learned a awful lot from this one thread.
Chris

bodhi808,

Thanks for the feedback.

You are very welcome. Glad I could help.

I also thought that the array sizes had to be declared initially rather than declaring the arrays as variant.

An array declared as Variant can hold any type of information, text, numbers as integer, long, double.

You can declare an array As String.

One thing from my basic understanding of arrays is that by default they start from position 0 unless specified, like in your ReDim line for array 'b'.

Because of the way my mind works, I usually define my arrays From 1 to some number.

'' create variant array b the same size as array a
'' b(1 t 45, 1 to 8)
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))

Does the 'a' array run from 0-7 and not 1-8?

In the VBA editor you would have to have the Locals window open, and, when you step thru the code you will see the in the Locals window how this works.

'' create variant array a(1 to 45, 1 to 8)
a = Range("A2:H" & lr)

I had thought that the Ubound function also counted from position 0?

LBound is the lower boundry of the array element which by design could start at 0 or 1.

UBound is the upper boundry of the array element.

variant array a(1 to 45, 1 to 8)

The 1 to 45 starts/refers to the rows of the array, beginning in LBound of 1 to UBound 45.

The 1 to 8 starts/refers to the columns of the array, beginning in LBound of 1 (column number 1) to UBound 8 (column number 8).

I assume they mustn't do because the loops cycle through the arrays without missing the last column when they both use 'ii' variable as the position across the array.

I use i variable to count/cycle vertically thru the a array.

I use the ii variable to count/cycle horizontally thru the a array.

I use the iii variable to count/cycle thru the b array when writing the rows in the b array.

'' using the i Long variable as a counter
''   loop from the first row of the array to the last row of the array
For i = 1 To UBound(a, 1)


  ''  *********************************************************** see below
  ''  we are checking each item in the second column of the array
  ''    if Left(a(i,2),2) = "00"
  ''    Or a(i,2) = ""
  ''    Or a(i,2) Like "*[A-Z,a-z]*" contains any alpha upper/lower case characters
  ''    then do not write the items in the row to the b array
  If Left(a(i, 2), 2) = "00" Or a(i, 2) = "" Or a(i, 2) Like "*[A-Z,a-z]*" Then
    'do nothing


  ''  then write the items in the row to the b array
  Else


    '' increment the iii Long variable by 1
    iii = iii + 1
    
    
    '' loop from the 1st 'column like' item of the a array to the last 'column like' item in the row
    For ii = 1 To UBound(a, 2)
    
    
      '' write the items in the a array to the b array
      b(iii, ii) = a(i, ii)
      
      
    '' and loop
    Next ii
  End If


''  And loop to the next row in the a array
Next i

Again, thank you very much for taking the time to help me understand this, I really appreciate it and have learned a awful lot from this one thread.
Chris

Come back anytime.