Alternative code to speed a macro

[olympiac]

It takes more than 5 minutes to run this code in a spreadsheet with more than 60.000 rows
The code below looks at the value in column "C" and if the value = Paris then the the relative cell in column "A" is updated with the value "code001"

Dim LastRow As Long
' Dim i As Long
' LastRow = Range("C" & Rows.Count).End(xlUp).Row
' For i = 2 To LastRow
' If Range("C" & i).Value = "Paris" Then
' Range("A" & i).Value = "code001"
' End If
' Next i
Is there any alternative to speed it up?

 

[JackDanIce]

Is this better?

Dim LastRow As Long
With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    LastRow = .Range("C" & Rows.Count).End(xlUp).row
    With .Range("A1:C" & LastRow)
        .AutoFilter
        .AutoFilter Field:=3, Criteria1:="Paris"
    End With
    .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible) = "code001"
    .AutoFilterMode = False
End With

 

[jvanbonn]

Check out this Site:

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

I'm thinking that encapsulating your code as below might help:

Sub yourSub()

[COLOR=Red][B]Application.ScreenUpdating=False[/B][/COLOR]

Dim LastRow As Long
    Dim i As Long
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("C" & i).Value = "Paris" Then
            Range("A" & i).Value = "code001"
        End If
    Next i
[COLOR=Red][B]
Application.ScreenUpdating=True[/B][/COLOR]

End Sub

 

[Weaver]

Here's another possibility

Sub paris()
    Application.ScreenUpdating = False
    lr = Range("C" & Rows.Count).End(xlUp).Row
    With Range("A2:A" & lr)
        .FormulaR1C1 = "=IF(RC[2]=""Paris"",""code001"","""")"
        .Value = .Value
    End With
End Sub

Of course, this won't work if there are values in the rows that don't equal "Paris" that you don't want to disturb.

 

[Weaver]

And here's another which avoids the issue with the previous solution.

Sub paris2()
    Application.ScreenUpdating = False
    With Range("C2", Range("C" & Rows.Count).End(xlUp))
        Set f = .Find(what:="Paris", lookat:=xlWhole)
        If Not f Is Nothing Then
            fs = f.Address
            Do
                f.Offset(, -2).Value = "Code001"
                Set f = .FindNext(f)
            Loop Until f.Address = fs
        End If
    End With
End Sub

 

[olympiac]

Thanks all for your replies. I will check it and let you know.