Inserting new row very slow in Excel 2003 vba

Question

I'm new to vba - I've literally only been doing it for 3 days.

Anyway, I've got a few forms that gets some data from the user, and I then write them to the top row of a seperate 'log' sheet. I'm using this:

With Worksheets("Log")
    .Unprotect
    .Range("A2").EntireRow.Insert Shift:=xlDown
    .Range("A2") = varToken
    .Range("B2") = varAction
    .Range("C2") = varLocation
    .Range("D2") = varTracking
    .Range("E2") = Date
    .Range("F2") = Time
    .Range("G2") = varPerson
    .Range("H2") = varOverride
    .Protect
End With

The trouble is, it flicks to the log sheet for half a second, and then takes ages to write.

The reason I have it writing to the top row of the log, is that I have the data summarised on the front sheet using 100 vlookups of varToken (there are 100 different tokens to look up), which find the first (i.e. top) entry in the log sheet.

I can write to the bottom of the log sheet if quicker, but then I'll need a code to replace the 100 vlookups that will look for the last mention of a token in potentially thousands upon thousands of rows, and will run quickly!

Thanks in advance!

Answer 1

I believe your Vlookups are slowing the process as they are getting recalculated every time you write to a cell. Try this

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

With Worksheets("Log")
    .Unprotect
    .Range("A2").EntireRow.Insert Shift:=xlDown
    .Range("A2") = varToken
    .Range("B2") = varAction
    .Range("C2") = varLocation
    .Range("D2") = varTracking
    .Range("E2") = Date
    .Range("F2") = Time
    .Range("G2") = varPerson
    .Range("H2") = varOverride
    .Protect
End With

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