在Excel2003vba中插入新行非常慢-InsertingnewrowveryslowinExcel2003vba

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

我是vba的新手 - 我实际上只做了3天。

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.

我把它写到日志的顶行的原因是，我在前面的表格中总结了使用100个varlook的vlookup（要查找100个不同的标记），它们找到了第一个（即顶部）条目在日志表中。

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!

如果更快，我可以写到日志表的底部，但是我需要一个代码来替换100个vlookups，这些代码可能会在成千上万行中找到最后一个令牌，并且会快速运行！

Thanks in advance!

提前致谢！

1 个解决方案

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