Need Help with Speed of VBA Code that works, but...

[Lidsavr]

I am running Excel 2007. I am a beginning to intermediate VBA programmer....

I have a large spreadsheet with over 2 years of data in it. It has over 155,000 rows. Because of different users, some of the data entry has different entries in column O than what it should be. The data in column O correponds to a part number in column P. My job is to clean the data up.

I wrote a quick do loop statement to search for a part number in column P, check the corresponding entry in column O and change it if needed.

The code works, but it takes almost 25 minutes to work through the spreadsheet in columns P:O one time (all 155,000 rows).

Can anyone help me speed this up?

Here is my do loop code:

Sub Correct_Data()

        Sheets("Data").Select
        Range("P2").Select
    Do
        If Selection = "AREC06099" Then
            ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate
            ActiveCell.Formula = "CAP .001UF CM 50V 5% C0G 0402"
            ActiveCell.Offset(rowOffset:=1, columnOffset:=1).Activate
        End If
            ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    Loop Until ActiveCell = ""

End Sub

 

[RSXchin]

at the beginning

application.screenupdating = false

and at the end

application.screenupdating = true

your screen will stop updating, but your code will be SO much faster.

 

[Weaver]

2 things

1. you don't need to 'select' a cell to determine or effect its contents
2. you don't need to look at every cell to 'find' a particular value

Here's a bit of code that might help

Sub correct2()
    Application.ScreenUpdating = 0
    With Sheets("data").Columns(16)
        Set f = .Find(what:="AREC06099")
        If Not f Is Nothing Then
            fs = f.Address
            Do
                f.Offset(, -1) = "CAP .001UF CM 50V 5% C0G 0402"
                Set f = .FindNext(f)
            Loop While fs <> f.Address
        End If
    End With
End Sub

 

[Lidsavr]

rsxchin,
Thank you very much. That worked!

Regards,

Lidsavr

 

[RSXchin]

How much did that speed it up? from 25 minutes that is.

 

[AlphaFrog]

This uses autofilter to filter column P on "AREC06099"
Then adds to column O "CAP .001UF CM 50V 5% C0G 0402" to just the visible filtered cells.

Sub Correct_Data2()

    Dim strItemP As String, strItemO As String
    
    strItemP = "AREC06099"
    strItemO = "CAP .001UF CM 50V 5% C0G 0402"

    Sheets("Data").Select
    
    Application.ScreenUpdating = False
    
    
    If Not Range("P:P").Find(strItemP, , , xlWhole, , , False) Is Nothing Then
    
        Columns("P").AutoFilter Field:=1, Criteria1:=strItemP
        
        Range("P2", Range("P" & Rows.Count).End(xlUp)). _
            SpecialCells(xlCellTypeVisible).Offset(, -1).Value = strItemO
                
        ActiveSheet.AutoFilterMode = False
                
    Else
        MsgBox "No match found for """ & strItemP & """"
    End If
    
    Application.ScreenUpdating = True
        
End Sub