VBA Code

@sdwalker

Sorry, didn't see your post. Do you want my input on your code?

T

@Sid, autofilter is even faster!

nutsch's code is much faster than mine as it uses autofilter. Nice one nutsch!!! Didn't think of that.

Sid

Sorry didn't see your post... :)

Sid

Thanks guys, all good!!


Cheers
Seamus

Thomas ... I'll always take your input on my code.  I'm always looking to improve.

@sdwalker
My comments in your code, knowing that I'm sure you took shortcuts to get a fast answer in.

Thomas

Sub Test()
'better to define all variables first
Dim i As Long, lastRow As Long

'lastRow = Sheets("Sheet0").Range("C20000").End(xlUp).Row
'handle the whole sheet when you run the lastRow, using rows.count
lastRow = Sheets("Sheet0").Cells(Rows.Count, "C").End(xlUp).Row

'most loops can be replaced by autofilter with specialcells(xlcelltypevisible)
'or a .find structure (if there are fewer lines)

For i = 1 To lastRow
'you can use With / End With to simplify reading
    With Sheets("Sheet0").Range("C" & i)
        If InStr(1, UCase(.Value), "NET DIFFERENCE") Then
          .Offset(0, -1).Value = .Value
          .Value = .Offset(-3, 0).Value
        End If
    End With
Next i

End Sub

Select allOpen in new window

Thanks for the help, Thomas.  You are correct that I took a couple of shortcuts, but I didn't know about the faster processing with Special Cells.

You would both probably be interested in my little test.   When I ran all of our code on the test data supplied, mine and Siddharth's were slightly faster (.016 sec vs .047 sec).  

nutsch       0.047
nutsch       0.047
nutsch       0.047
sdwalker       0.016
sdwalker       0.016
sdwalker       0.016
siddharth       0.016
siddharth       0.016
siddharth       0.016

When I multiplied the recordset about 30 times (going to row 6600 instead of row 183), nutsch's was faster than mine or Sid's (I did add the screen updating to mine, as I usually do that for large datasets).

nutsch       0.109
nutsch       0.141
nutsch       0.109
nutsch       0.125
nutsch       0.109
sdwalker       0.281
sdwalker       0.281
sdwalker       0.266
siddharth       0.297
siddharth       0.266
siddharth       0.281
siddharth       0.281

So in this case, I'm not finding the .Find to be much faster.

Thoughts?

Correction, I just added screenupdating to sid's code and it now comes in second for the longer dataset.

siddharth       0.172
siddharth       0.172
siddharth       0.172
siddharth       0.188

Interesting ...

I guess the .Find is still a loop in a way
On my code, removing the cl loop might speed it up for high number of rows, but I'm not sure.

T

Sub asdgasdga()
Dim cl As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("a1:C" & Cells(Rows.Count, "C").End(xlUp).Row).AutoFilter field:=3, Criteria1:="NET DIFFERENCE"

With Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    .Offset(1).SpecialCells(xlCellTypeVisible).Offset(, -1) = "NET DIFFERENCE"
    .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=R[-3]C"
    
    .Offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=R[-3]C"
    
    ActiveSheet.AutoFilterMode = False
    
    Columns(3).Copy
    Columns(3).PasteSpecial Paste:=xlPasteValues
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

sdwalker: For relatively small number of rows say for example 10 Rows, looping is fine and even I would prefer that over .Find and Autofilter as that is too much of code to write just for 10 rows.

However as the rows go up considerably say 6000 or let's say 20000 then yes, I would give the following preference to codes.

1) Autofilter
2) .Find
3) Looping

Let me do some test results.

Sid

Ok here it is. I have attached a sample file so that you may want to test it your self.

I just added sheet names in Thoma's code and removed Screenupdating from his code.

Sid
Test.xls
Untitled.jpg

Sorry wrong picture...

Sid

Anyways run the test and see what do you get.

Sid

Also if you want, you can replace tickcount with Now.

Sid