lundi 19 juin 2017

I am using an if statement nested in a loop that is slowing down my code. What can I do to speed it up?

I have an if statement nested in a loop that I am using to clean up imported data. The if statement evaluates the value of the active cell and then deletes the row of the active cell if it meets certain criteria. I'm wondering if there's another way to code this so it's not referencing the spreadsheet for every iteration, and consequently making it run faster than it currently is. Any tips would be appreciated. Code I am using is below:

Sub copy_RawAvgDem()

Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet

Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("M:\FAST 
team\Inventory_Planning\2016_05_FG_Inv_targets.xlsx")
Set sht1 = wkb1.Sheets("RawAvgDem")
Set sht2 = wkb2.Sheets("Model")

sht2.ShowAllData
sht2.Cells.Copy
sht1.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb2.Close False

Worksheets("RawAvgDem").Activate
Range("AN2").Select

Do Until IsEmpty(ActiveCell.Value)
    If ActiveCell.Value = "MTO" Then
        Rows(ActiveCell.Row).EntireRow.Delete
    Else: ActiveCell.Offset(1, 0).Select
    End If
Loop

End Sub

Aucun commentaire:

Enregistrer un commentaire